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:

A strange blue cyber background with black lines going vertical and horizontal. The image of a woman takes up a third of the frame and she is starting at a the small image of a meme where someone is wearing a rainbow afro. "Support MindFuel Blog on Patreon" is meticulously crafted into a graphical overlay which obfuscates part of the underlying artistry. So sad.
  • 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:
      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
    • 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.


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.


Leave a Comment

Your email address will not be published. Required fields are marked *