Adding auto incrementing primary key to an existing table in SQL SERVER 2005

Question

I have a table with 8,000 rows of data and will be adding more. but I forgot to put a primary key in the beginning. so that each row has a unique key. later i added a primary key column. but that column is NULL now.

I want the first row to start at ID 1 and increment all the way up to the last row at ID 8000. How do I update all of the rows with a single query?

i am using SQL Server 2005.

Answer 1

Open Table Design, Add New Column u want Select the column and in Properties In Identity Specification make (Is Identity) Yes.. You can start from where you want by setting the Identity Seed property, by Default it starts from 1.

If you have already Identity Column u can also update it.
Step 1: Remove Identity Specification from Column in Table Design.
Step 2: Use Cursor to Update table Column starting from 1.
Step 3: Again apply Identity Specification on Column in Table Design
Step 4: By query reset Identity Column, from the value u want. e.g
DBCC CHECKIDENT("TableName",Reseed,8000);
so the next identity value will be 8001.