How to create increment script in sql server

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
LVL 1
nocinfospanAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
-- 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
 
cyberkiwiCommented:
I don't follow what you are after, can you please provide some sample data and what the result should look like?
0
 
nocinfospanAuthor Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
cyberkiwiCommented:
You want the string LITERAL "firstName1" ! ok..
0
 
cyberkiwiCommented:
;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
 
nocinfospanAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.