CODEWITHSUNDEEP
sqlsql-server
SeyoS
SeyoS

Reputation: 681

Add a column ID with a unique value for each row

I have an existing table with existing data and I want to add new column (named ID) with auto-increment and I want to add a unique value for each row.

Is there an other way than fetching all data and do an update for each row to set this value ?

Upvotes: 0

Views: 6427

Answers (1)

Ionic
Ionic

Reputation: 3935

If you need it in a SELECT:

SELECT *, ROW_NUMBER() OVER(ORDER BY ...A ORDER VALUE) as id
FROM yourTable

If you need it in your table:

ALTER TABLE yourTable ADD id int identity(1,1)

Here is a demo for the output of the ALTER TABLE:

CREATE TABLE #temp(name nvarchar(50))

INSERT INTO #temp(name) VALUES(N'Kai'),(N'Bernd'),(N'Flo'),(N'Pete')

SELECT * FROM #temp

-- This is what you need to do
ALTER TABLE #temp 
ADD id int identity(1,1) -- This will add and fill the new column

SELECT * FROM #temp

DROP TABLE #temp

Upvotes: 4

Related Questions