Gunnar Vøyenli (gv@edbkonsulent.no) writes:
I have an existing table (t) with a column that is NOT an identity column
(t.ID), but it has manually inserted "row numbers". I want to make this
column become an identity column. This column is a key field to other
tables, so I want to keep the row numbers that are allready inserted.
From the Query Analyzer, how do I do this?
There is no convenient ALTER TABLE command for this. (Except in SQL
Server CE!). So you need to:
1) Rename the existing table and any constraints it may have.
2) Run a CREATE TABLE statement with the new definition of the column,
including constraints for the table.
3) Isssue SET IDENTITY_INSERT ON for the table to permit inserting of
explicit values in the IDENTITY column.
4) INSERT newtbl (...) SELECT ... FROM oldtbl.
5) Move referencing foreign keys to refer to the new table.
6) Restore triggers and indexes on the new table.
7) drop the old table.
From the Enterprise Manager you can carry out this by point-and-click.
However, what EM performs behind the scenes is something like the above.
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
