Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Update a table column with a sequence number v3

By richardd, 2006/04/26

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