CODEWITHSUNDEEP
sql-server
Jesuraja
Jesuraja

Reputation: 3844

Add Identity sequence values to Column ID

I have a table and except first column (ID), all other columns populated with records (more than 10000 records). I want to fill the ID column with sequence numbers (1,2,3,4...) for all the records.

enter image description here

Expected output:

enter image description here

I have tried the following statement:

SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ID, Name FROM TableName

But not able to use the ROW_NUMBER() in UPDATE statement.

How to do that?

Upvotes: 1

Views: 91

Answers (2)

StuartLC
StuartLC

Reputation: 107277

You can't alter an existing column to be an IDENTITY, but you can add a new identity column (provided that there is only 1 identity on the table). Given that your existing ID contains all NULL's, why not drop and re-add?

ALTER TABLE MyTable DROP Column ID;
GO

Alter Table MyTable Add ID Int Identity(1, 1)
Go

SqlFiddle here

Upvotes: 1

Arion
Arion

Reputation: 31239

Maybe something like this:

Test data:

DECLARE @tbl TABLE(ID INT,Name VARCHAR(2))

INSERT INTO @tbl
VALUES
    (null,'A'),
    (null,'B'),
    (null,'C'),
    (null,'D'),
    (null,'E')

Then update statement like this:

;WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY name) AS rowNbr,
        tbl.*
    FROM
        @tbl AS tbl
)
UPDATE CTE
    SET CTE.ID=CTE.rowNbr

Then the output from this:

SELECT * FROM @tbl

Will be this:

1   A
2   B
3   C
4   D
5   E

Upvotes: 2

Related Questions