sql - set identity on the column

Question

How can I modify table and set identity on PK column using T-SQL?

thanks for help

Answer 1

You can't modify an existing column to have the IDENTITY "property" - you have to:

• create a new table with the same structure (but with IDENTITY set up),
• turn on IDENTITY_INSERT for this new table,
• insert rows from the old table into the new table,
• drop the old table, and,
• rename the new table to have the old table name.

If there are foreign keys involved, you need to fix those up also.