?
Solved

How to create increment script in sql server

Posted on 2010-11-23
6
Medium Priority
?
244 Views
Last Modified: 2012-05-10
I need to create a script in sql server 2005 table that has first, middle and last name. below mentioned is the scanrio.
 First Name with ‘Firstnamen’ where n is an incrementing number starting from 1, Middle Name with ‘Middlenamen’ where n is an incrementing number starting from 1, Last Name with ‘Lastnamen’ where n is an incrementing number starting from 1

any help would be highly appriciated
0
Comment
Question by:nocinfospan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34199019
I don't follow what you are after, can you please provide some sample data and what the result should look like?
0
 
LVL 1

Author Comment

by:nocinfospan
ID: 34200354
Hi,

I have over million records with first, middle and last names. so please see below

original record of first name   === scramble them to
jhon                                                firstname1
tim                                                    firstname2
kevin                                               firstname3
....                                                    ....firstname10000000
and same goes for middle and lastnames
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34200361
You want the string LITERAL "firstName1" ! ok..
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34200382
;with tmp as (
select *,
newfirstname='firstname'+CONVERT(varchar(10), ROW_NUMBER() over (order by newid())),
newlastname='firstname'+CONVERT(varchar(10), ROW_NUMBER() over (order by newid())),
newmiddlename='firstname'+CONVERT(varchar(10), ROW_NUMBER() over (order by newid()))
from tbl)
update tmp set
firstname = newfirstname,
lastname = newlastname,
middlename = newmiddlename

The n's won't be in sync so you may get firstname1, middlename1231222, lastname555
If you want them in sync, put a specific order by column list instead of newid()
0
 
LVL 1

Author Comment

by:nocinfospan
ID: 34206570
Thanks for the script, I tested it out in the following way and it worked.
1. SELECT TOP 10 * INTO #mytable FROM TABLE1
2. select *, newfirstname='Fname'+CONVERT(varchar(10), ROW_NUMBER() over (order by elector_id)),
newlastname='Lname'+CONVERT(varchar(10), ROW_NUMBER() over (order by elector_id)),
newmiddlename='Mname'+CONVERT(varchar(10), ROW_NUMBER() over (order by elector_id))
from #mytable

Now I have a source table called 'Table1' that has all the first_name,middle_name and last_name columns that needs to be changed to fname1..n, mname1...n and lname1...n. please help me to write a customize script that does that as per the sample i mentioned above. When i do it my self i get syntaz errors
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 34207702
-- drop table #mytable

SELECT TOP 10 * INTO #mytable FROM TABLE1

;with tmp as (
select *,
newfirstname='Fname'+CONVERT(varchar(10), ROW_NUMBER() over (order by elector_id)),
newlastname='Lname'+CONVERT(varchar(10), ROW_NUMBER() over (order by elector_id)),
newmiddlename='Mname'+CONVERT(varchar(10), ROW_NUMBER() over (order by elector_id))
from #mytable)
update tmp set
firstname = newfirstname,
lastname = newlastname,
middlename = newmiddlename

Change the 3 parts in bold to the 3 field names you want to modify
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

766 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question