Convert an existing Column to Identity

Question

I have a table in SQL Server with bundle of records. I want to convert the ID column which is Primary Key to an identity Column without loss of data. I thought of the following two approaches:

1. Create a new table with identity & drop the existing table.
2. Create a new column with identity & drop the existing column.

but it's clear that they can not be implemented because keeping records is my first priority.

Is there another way to do this?

Answer 1

This solution violates your point 2, but there is no other way and I think your aim is to keep the old values, because nothing else makes sense...

You could do the following:

1. make it possible to insert into identity columns in your table:

   set identity_insert YourTable ON
   

2. add a new ID column to your table with identity and insert the values from your old columns

3. turn identity insert off

   set identity_insert YourTable OFF
   

4. delete old ID column
5. rename new column to old name
6. make it to the primary key

The only problem could be that you have your ID column already connected as foreign key to other tables. Then you have a problem with deleting the old column... In this case you have to drop the foreign key constraints on your ID column after step 3, then do step 4 to 6 and then recreate your foreign key constraints.