Solved

How to create increment script in sql server

Posted on 2010-11-23
6
243 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 500 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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 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