Normally, trying to insert data into an identity column (autoincremental column) results in an error message. However, there are cases when we need to do this, and using SET IDENTITY_INERT, it is possible. Here is how:
SET IDENTITY_INSERT MyTable ON INSERT INTO MyTable(MyTableId, ...) VALUES (42, ...) SET IDENTITY_INSERT MyTable OFF
Here is the message you will get if you don’t do this:
Cannot insert explicit value for identity column in table ‘MyTable’ when IDENTITY_INSERT is set to OFF.
This is valid only for inserts. It is not possible to update an identity column using this method. If you want to change a row id, you will have to re-insert the row with the new id and delete the original one. Let’s say you want the row with id 42 to really have id 100:
SET IDENTITY_INSERT MyTable ON INSERT INTO MyTable (MyTableId, ...) SELECT 100 AS MyTableId, ... FROM MyTable WHERE MyTableId=42 DELETE FROM MyTable WHERE MyTableId=42 SET IDENTITY_INSERT MyTable OFF