Articles


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








Valid XHTML 1.0!

There is 1 user online

1,058,897 total unique visitors
2,041,563 total pageviews
108 visitors in the last 24 hours
20 total visitors today
49 pageviews today
This page has been visited 59,456 times

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