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 explicity set the ID of the record you are entering.

To insert records with the ID explicity 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.

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 in its entirety:

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

There is 1 user online

1,014,773 total unique visitors

1,912,202 total pageviews

136 visitors in the last 24 hours

31 total visitors today

127 pageviews today

This page has been visited 55,629 times

Most users online at once:
52 on 12/13/2009

Articles

IDENTITY_INSERT is set to OFF