How to add an identity to an existing column?

[dullboy]

I wander if it is possible to add an identity to an existing column in SQL server? If yes, could you please give an example. Thanks for your inputs.

[kevin.horgan]

I would suggest you create a new empty table with the same structure as your existing table except the column you wish to change is now of type IDENTITY and a different table name.

Then INSERT all records from your old table to the new table but first SET IDENTITY_INSERT as decribed in the following link. Make sure you provide a unique value for your new identity column.

http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server

Then turn off IDENTITY_INSERT.

Finally drop the original table (or rename it out of the way as a back up). Then rename the new table to have the name of the old table. Look at the system procedure "sp_rename" for renaming tables.
sp_rename 'old_table_name', new_table_name'

I hope that helps.

Cheers,

Kevin