February 23, 2009 at 9:31 pm
i have a table with more than 50,000 rows and i need to generate alphanumeric
id to identify each employee. The id should be like this A000001, A000002 and
for the name starts with it should be like B000001 and so on...
The stored procedure, I prepared to loop through the entire table to
update each record is very slow...
How to do it in a single update statement
table structure
uid varchar(6)
emp_name varchar(50)
the format of uid is like, A00001, A00002... and B00001 for the name starts with 'B'
any help..
February 23, 2009 at 10:51 pm
Like this:
UPDATE E
Set EmpID = Left(LastName,1)
+ RIGHT('000000'
+ ROW_NUMBER() Over(Partition By Left(LastName,1) Order by LastName)
, 6)
From Employees E
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 10:56 pm
See this example on adventureworks database
USE AdventureWorks
GO
SELECT firstname
,LEFT (Firstname,1) AS First
,row_number()over(PARTITION BY LEFT (Firstname,1) ORDER BY Firstname) as Seq
,LEFT (Firstname,1)+RIGHT('00000'+CONVERT(VARCHAR(10),row_number()over(PARTITION BY LEFT (Firstname,1) ORDER BY Firstname)),5) newseq
FROM Person.Contact
order by Firstname
On similar lines you can write a UPDATE statement as
UPDATE Tablename
SET UId = LEFT (Firstname,1)+RIGHT('00000'+CONVERT(VARCHAR(10),row_number()over(PARTITION BY LEFT (Firstname,1) ORDER BY Firstname)),5)
John Smith
February 24, 2009 at 12:42 am
hi, thanx a lot for your replies.... it's working fine...
February 24, 2009 at 7:07 am
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply