SQL Server Performance Forum – Threads Archive
Update Identity Column
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? Thanks, StarWarsBigBangNo easy solution. You have to drop the IDENTITY property, do your UPDATE stuff and recreate the IDENTITY property. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
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 Cheers
Twan
Nope, that doesn’t work. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
well it was worth a try <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />
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? Regards, Robert.
Or write into new table – tmp_oldTablename, then drop old table and sp_rename new one.
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.
Thanks, StarWarsBigBang
]]>