AlphaNumeric Increment using SQLQuery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

AlphaNumeric Increment using SQLQuery

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-10-01 : 17:07:35

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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-10-02 : 03:59:14

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.

imrul
Starting Member

36 Posts

Posted - 2013-10-02 : 04:21:38

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

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-10-02 : 10:04:29

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.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-10-02 : 11:22:45

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.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-02 : 11:51:44

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

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-10-02 : 12:24:59

hi Scott ,

your suggestion works for me perfectly. Thank you and thanks everyone for helping on this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources