Solved

How to create increment script in sql server

Posted on 2010-11-23
6
242 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 Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need SSIS project 2 30
SQL Syntax 6 32
access to sql migration 5 24
SQL Distinct Question 3 15
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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 shrink a transaction log file down to a reasonable size.

733 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