SQL Server 2008: Adding an Identity to Existing Column

No. You are probably not.  Here is a SHORT ANSWER (just to show the concept) for putting an IDENTITY on an existing column, based on what I’ve seen:

• Use All Tasks -> Generate Scripts to script the schema of the table(s) you want to alter.  Script it to an SSMS query window.
• Go through the script and put a “2” on the end of the table name(s), and  add “IDENTITY(1,1)” to the int column you want to make an identity:
  • e.g. CREATE TABLE MyTable2 (…
    MyIDColumnName int IDENTITY(1,1) …
• Run the script (I do this one table at a time) – this creates a duplicate table that HAS the identity column you want.
• For each table you will need to run a script like this:
  • SET IDENTITY_INSERT MyTable2 ON
    INSERT INTO MyTable2 ([you must list out all the columns…sorry, SSMS requires this])
    SELECT [yes you must list all the columns again, in the SAME order] FROM MyTable
    SET IDENTITY_INSERT MyTable2 OFF
  • Note: Notice the select is from your ORIGINAL table – no “2” on the table name, because you are inserting the data from the table that doesn’t have the identity column to the one that does
• Now you can drop your original tables, and then rename the new tables by getting rid of the “2” on the name.

NARRATIVE

Now, I hope someone finds a better way – that would be great.  I think this approach, if it IS the answer, is lame.  We are in the year 2010.  2010!!?? And I can’t get a column to populate automatically with integers unless I do the above steps? C’mon!! But, for serious, when I upgraded from 2005 to 2008…somehow the script I generated didn’t have the “IDENTITY” specs or something… not sure.  Later, I found that adding the identity via SSMS threw an error saying the table would need to be dropped and recreated.  I also found I couldn’t delete the column (I can remove its primary key designation, but it complained if I tried to delete it because “it is in use by other objects”).  So, the fall back is to drop and recreate the table – hence the magical dance outlined above.

Arg.