SQL Server Performance

Update Identity Column

Discussion in 'General Developer Questions' started by starwarsbigbang, May 16, 2006.

  1. starwarsbigbang New Member

    Just wanted to know if we can update Identity Column. There was a requirement where I had to change the value of this column which was an identity column.

    I changed the same using:

    dbcc checkident('tablename',reseed,New_reseed_value)

    After this the seed value would change. Now I had to update this column to some value+4 for this entire column. I get the error message 'Cannot update identity column' Any solutions?


  2. FrankKalis Moderator

  3. Twan New Member

    Hi ya,

    Have you tried using SET IDENTITY_INSERT <tablename> ON and then issuing the update (followed by turning the IDENTITY_INSERT off again? not sure if this works for updates

  4. FrankKalis Moderator

  5. Twan New Member

    well it was worth a try <img src='/community/emoticons/emotion-5.gif' alt=';-)' />
  6. RGKN New Member

    However you can write it all to a holding table (possibly temporary) Then truncate the original table,
    SET IDENTITY_INSERT <tablename> ON
    reinsert with the id column +4 and reseed the identity column as required.
    then switch IDENTITY_INSERT off again.

    How big is the table?



  7. mmarovic Active Member

    Or write into new table - tmp_oldTablename, then drop old table and sp_rename new one.
  8. starwarsbigbang New Member

    Thanks for all your suggestions!!

    Twan and mmarovic's solutions are the closest ones. SET IDENTITY_INSERT ON will work only for new updates. We will have to bcp out the data and then do it again. That's why writing to temp table and renamin would have been a better option.



Share This Page