Little SQL Server Tricks: How to Fix “Cannot insert explicit value for identity column”

When you let SQL Server create the primary keys for a table and try to insert a specific key, you will get this error message:

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

Fore once the error message says exactly what the problem is. In this case, your INSERT statement fails because IDENTITY_INSERT is set to OFF. You need to change it to ON, insert your rows and then turn it to OFF again. Otherwise, all following statements need to specify the identity key as well.

Add the two calls to SET IDENTITY_INSERT before and after your statement and the error is gone:

This little change to the table is a great help when you need to create test data that demands a specific Id. Should you need those changes as part of your daily routine, you better get rid of the automatically created primary keys and always specify a unique value with your INSERT statements.