SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 AlphaNumeric Increment using SQLQuery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 10/01/2013 :  17:07:35  Show Profile  Reply with Quote
i have a table "Employee" has the columns ("Fname", Lname, emailaddress).
I am trying to backup the database from production and move it to Test database. For security purpose i should not have actual names and email address. so i wanted to update the Fname,Lname as dummy names on all the rows, as follows,

FName LName

FName1 LName1
FName2 Lname2
Fname3 Lname3
FName4 LName4
.
.
.
.

It is possible to do this without looping all the rows and performing one by one. Can i t done with sqlquery itself.

Any samples please

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/02/2013 :  03:59:14  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
select distinct fname, identity(int,1,1) seq into #a from tbl
select fname, newname = 'FName' + convert(varchar(10),seq) into #b from #a
update tbl set FName = b.newname
from tbl t
join #b b
on t.fname = b.fname


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

imrul
Starting Member

Bangladesh
36 Posts

Posted - 10/02/2013 :  04:21:38  Show Profile  Reply with Quote
Please try with the following query. Here Fname & email address are concatenated to ensure uniqueness of the record.

select cast(DENSE_RANK() OVER (ORDER BY ln.Fname+ln.emailaddress) as varchar(150))+'Fname' as FirstName
, cast(DENSE_RANK() OVER (ORDER BY ln.Fname+ln.emailaddress) as varchar(150))+'Lname' as LastName
, cast(DENSE_RANK() OVER (ORDER BY ln.Fname+ln.emailaddress) as varchar(150))+'mail@test.com' as Email
from Employee ln
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 10/02/2013 :  10:04:29  Show Profile  Reply with Quote
Hi Imrul and nigelrivett thanks for your reply. How can this select query will be used with update statement? as i said i need to update the existing records with this select query output.

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/02/2013 :  11:22:45  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Have a look at my previous post - the update statement is at the end.
You might want to index the temp table or do it in batches if the table is big.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
411 Posts

Posted - 10/02/2013 :  11:51:44  Show Profile  Reply with Quote
I'd try to avoid any ROW_NUMBER() or other sorting.

Is there a unique employee id in the table? (but not ssn for obvious reasons :-) )



UPDATE dbo.tablename
SET 
    fname = 'FNAME_' + CAST(emp_id AS varchar(10)),
    lname = 'LNAME_' + CAST(emp_id AS varchar(10))

Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 10/02/2013 :  12:24:59  Show Profile  Reply with Quote
hi Scott ,

your suggestion works for me perfectly. Thank you and thanks everyone for helping on this.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000