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