Fixing an IDENTITY_INSERT SQL error

IDENTITY_INSERT is set to OFF. Cannot insert explicit value for identity column in table 'tablename' when IDENTITY_INSERT is set to OFF.

This error occurs when you try to insert a row into a table that has a column that auto-generates an ID. Although most times this is how you want the table to perform, at times you may need to explicitly set the ID of the record you are entering. To insert records with the ID explicitly set, you need to allow identity insert. After you insert the record, turn off identity insert again. You need to specify the table you are specifying this for.

Steps to enter data into a table when you receive the error 'IDENTITY_INSERT is set to OFF. Cannot insert explicit value for identity column in table...'

In the same window as you are running your SQL statement, run the following (where [database.table] is the name of your database and table which you are trying to insert the record):

set IDENTITY_INSERT [database.table] ON

After you run your SQL query, remember to turn identity insert off again:

set IDENTITY_INSERT [database.table] OFF

Sample code to fix the error 'IDENTITY_INSERT is set to OFF. Cannot insert explicit value for identity column in table'

set IDENTITY_INSERT dbo.site_users ON
INSERT INTO site_users (user_id, username, password) values (24, ''test'', ''password'')
set IDENTITY_INSERT dbo.site_users OFF