You cannot insert an explicit value to an identity column in table ' OrderList ' when IDENTITY_INSERT is set to OFF

Problem description: In SQL SERVER 2008, when inserting data into a field in a data table, an error is given, as follows:

When IDENTITY_INSERT is set to OFF, you cannot insert an explicit value into the identity column in table ' OrderList '.

Solution:

1 --allows explicit values to be inserted into the identity column of the table on-allow off-not allowed2 Set Identity_insertOrderList on--Open
3 4 Insert  intoorderlist (id,ordername,createdate)5 Values(4520,'Set',getdate())6 7 Set Identity_insertOrderListOFF--Close

In this execution of the INSERT statement, there is one more switch setting, set the following:

1 --Syntax:2 3 SET Identity_insert [database. [Owner.]] {Table} { on | OFF } 4 allows explicit values to be inserted into the identity column of a table5 6 parameter Description:7 Database: For Database8 Table: For a table9 Ten  on: Allows inserting an explicit value into an identity column One OFF: Not Allowed

Attention:

(1)) at any time, only one table of the Identity_insert property can be set to ON. If a table has set this property to on, and a set IDENTITY_INSERT ON statement is issued for another table, an error message is returned stating that the SET IDENTITY_INSERT is set to on and reports that this property has been set to on.

(2) If the insertion value is greater than the current identity value of the table, SQL Server automatically uses the newly inserted value as the current identity value.

(3) Set IDENTITY_INSERT settings are set at run time, not at parse time.

You cannot insert an explicit value to an identity column in table ' OrderList ' when IDENTITY_INSERT is set to OFF