0

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 ?

JackWhiteIII
  • 1,388
  • 2
  • 11
  • 25
SeyoS
  • 661
  • 5
  • 22
  • 3
    this topic is duplicate [http://stackoverflow.com/questions/4862385/sql-server-add-auto-increment-primary-key-to-existing-table] – Diabolus Jun 18 '15 at 09:45

1 Answers1

5

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
Ionic
  • 3,884
  • 1
  • 12
  • 33
  • By adding the column ID, it won't set a value for each row, Isn't ? Each value will be NULL. – SeyoS Jun 18 '15 at 09:42
  • @SeyoS - Not if the column is defined as `identity(1,1)` – ughai Jun 18 '15 at 09:44
  • Hi, I've added a demo to show you the output. The values will be inserted not null. @Tanner And no, partition by is if you want to group it and number it per group. – Ionic Jun 18 '15 at 09:44