Menu
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
1000
You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.
Only one identity column per table is allowed. So, no, you can't have two identity columns.
Steps for updating existing identity column valuesRemove all the foreign key constraints referencing the identity column. Copy all the records from the identity table and insert it to a staging table. Now, switch ON IDENTITY_INSERT for the identity table to allow inserting values to the identity Column.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
