Update a table column with sequence number

,

Assume that you already have a table with 1000 rows. Now suppose, after some months, you feel a need to insert a sequence number column in the same table. Here is a script that I have worked out.

If you have anyother better way then you are most welcome to comment on the same!

/*
==================================================================
This query helps you to insert a sequence number in the table.
It is useful when you have decided to add the sequence type of column quite late. 
===================================================================
CREATE TABLE tableA (SeqId Numeric(10), FirstName Varchar(50), LastName Varchar(50))
INSERT INTO tableA VALUES (NULL, 'abc', 'xyz')
INSERT INTO tableA VALUES(NULL, 'pqr', 'sql')
*/

DECLARE @mStart Numeric(20,0)
--Initialize the sequence variable
SET @mStart = 0

--Declare a cursor to select rows from the table
DECLARE SeqCursor CURSOR LOCAL FOR
SELECT * FROM [tableA]

--Open the cursor
OPEN SeqCursor

--Fetch from the cursor
FETCH SeqCursor

--Loop while fetch status is 0

WHILE (@@FETCH_STATUS = 0)
	BEGIN
		--Increment the number
		SET @mStart = @mStart + 1

		--Update the table column
		UPDATE [tableA] SET [SeqId] = @mStart
		WHERE CURRENT OF SeqCursor
	
		FETCH NEXT FROM SeqCursor
	END

/*
See the output
SELECT * FROM [tableA]
*/

Rate

4 (2)

Share

Share

Rate

4 (2)