[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to create increment script in sql server

Posted on 2010-11-23
6
Medium Priority
?
251 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

649 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