Technical Article

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)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (2)

You rated this post out of 5. Change rating