How to change an identity column value in SQL

April 3, 2012 at 10:14 AMMadestro

It's not very obvious to the non-db-admin type but you can't just override an identity value in a table, even if you set identity_insert ON.

What you need to do instead is:

  • Turn identity_insert ON
  • Delete the record you need to change
  • Insert it again with the desired identity value
  • Turn identity_insert OFF

Here is the code snippet:

SET IDENTITY_INSERT YOUR_TABLE ON
DELETE FROM YOUR_TABLE WHERE YOUR_IDENTITY_FIELD = THE_ID_YOU_NEED_TO_CHANGE
INSERT INTO YOUR_TABLE (YOUR_IDENTITY_FIELD, ...[other fields]) VALUES (THE_NEW_ID,  ...[other fields])
SET IDENTITY_INSERT YOUR_TABLE OFF

Posted in: Databases

Tags: