Post reply

Adding identity to an existing column

  • July 19, 2007 at 9:53 am

    #173852

    If i try to add an identoty to an existing column which is not null and unique I run into problems. This is what i am trying. 

    alter table TABLENAME alter column COLUMN int IDENTITY (1,1)

    Does anyone have any suggestions how  to do this.

    TIA

     

  • July 19, 2007 at 10:28 am

    #720846

    As far as I know this is pretty hard to do.

     

    The only thing I can think of is to create a new table with the correct properties set up.  Then do insert into newtable select ... from oldtable...

    while having SET IDENTITY_INSERT newTable ON.

    Then set it off after you're done.  I don't know any other way from the top of my head.  You may have to change the seed value to make sure you don't get errors of duplicate keys in the table (if you start the seed at 1 and you already have data from 1 to n).

  • July 20, 2007 at 12:41 am

    #721014

    You could try adding a new field (TempID) to temporarily hold the values you currently have in your field, remove the old field and the add it back on to the table with the identity attribute in place, copy the values back into it from the TempID field and remove the TempID field. Depending on the size of the table this might be faster.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply