Technical Article

Update a table column with a sequence number v3

,

Both Sachinvaishnav and Mark Chad have submitted scripts to populate a column with a sequence number. The first solution uses a cursor; the second uses the SQL 2005 ROW_NUMBER() function.

v1:
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1643
v2:
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1664


Since neither script requires a specific order for the sequence number, the same result can be achieved with a single UPDATE statement.

-- Create and populate a temporary table:
CREATE TABLE #TableA
(
SeqId int,
FirstName varchar(50),
LastName varchar(50)
)

DECLARE @Counter int
SET @Counter = 0
WHILE @Counter < 1000
BEGIN
INSERT INTO #TableA VALUES (Null, 'abc', 'xyz')
SET @Counter = @Counter + 1
END


-- Update the sequence number:
DECLARE @SeqId int
SET @SeqId = 0

UPDATE
#TableA
SET
@SeqId = SeqId = @SeqId + 1


-- Return the data and remove the table:
SELECT * FROM #TableA
DROP TABLE #TableA

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating