Cannot insert explicit value for identity column in table ‘MyTable’ when IDENTITY_INSERT is set to OFF

When you try to insert data into a table with an IDENTITY column, the following error message may be returned.

 

Cannot insert explicit value for identity column in table ‘MyTable’ when IDENTITY_INSERT is set to OFF.

 

You will need to enable IDENTITY_INSERT, using the following statement.

 

SET IDENTITY_INSERT MyTable ON

GO

INSERT INTO MyTable

(Id, Name)

VALUES (123, ‘My name is Martin’)

GO

— Be reminded to reset IDENTITY_INSERT

SET IDENTITY_INSERT MyTable OFF

 

=== For more information ===

~ SET IDENTITY_INSERT (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms188059.aspx