SQL Server: how to add new identity column and populate column with ids?

sql sql-server cursor primary-key
Sergejs picture Sergejs · Feb 23, 2012 · Viewed 131.4k times · Source

I have a table with huge amount of data. I'd like to add extra column id and use it as a primary key. What is the better way to fill this column with values from one 1 to row count

Currently I'm using cursor and updating rows one by one. It takes hours. Is there a way to do that quicker?

Thank you

Answer

marc_s picture marc_s · Feb 23, 2012

Just do it like this:

ALTER TABLE dbo.YourTable
ADD ID INT IDENTITY(1,1)

and the column will be created and automatically populated with the integer values (as Aaron Bertrand points out in his comment - you don't have any control over which row gets what value - SQL Server handles that on its own and you cannot influence it. But all rows will get a valid int value - there won't be any NULL or duplicate values).

Next, set it as primary key:

ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable PRIMARY KEY(ID)

Related questions

SQL Call Stored Procedure for each Row without using a cursor

How can one call a stored procedure for each row in a table, where the columns of a row are input parameters to the sp without using a Cursor?

Read More
Cursor inside cursor

Main problem is about changing the index of rows to 1,2,3.. where contact-id and type is the same. but all columns can contain exactly the same data because of some ex-employee messed up and update all rows by contact-id and type. …

Read More
Get all dates between two dates in SQL Server

How to get all the dates between two dates? I have a variable @MAXDATE which is storing the maximum date from the table. Now I want to get the all dates between @Maxdate and GETDATE() and want to store these …

Read More