You can specify an explicit value for an identity column in a table only if a list of columns is used and IDENTITY_INSERT is on

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

Example:

1. First create a table with an identity column:
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar (40))

2. Try the following in the table:
INSERT into products (ID, product) VALUES (3, ' garden shovel ')

The result is an error: "When IDENTITY_INSERT is set to OFF, an explicit value cannot be inserted into the identity column in table ' products '. ”

3. Switch to:
SET Identity_insert Products on
INSERT into products (ID, product) VALUES (1, ' garden shovel ')

Returned correctly.

4. Create another table products2, and try the same insert operation:
CREATE TABLE products2 (id int IDENTITY PRIMARY KEY, product varchar (40))

Then execute:
SET Identity_insert products2 on
INSERT into Products2 (IDs, Product) VALUES (1, ' garden shovel ')

Cause error: "Table ' Material.dbo.products ' Identity_insert is already on." Cannot perform SET operation on table ' Products2 '. ”

Instead, execute:
SET Identity_insert Products OFF
SET Identity_insert products2 on
INSERT into Products2 (IDs, Product) VALUES (2, ' garden shovel ')

Implementation passed.

5. Try the following:
SET Identity_insert products2 on
INSERT into Products2 SELECT * FROM Products

Cause error: "You can specify an explicit value for the Identity column in table ' products2 ' only if the list of columns is used and IDENTITY_INSERT is on. ”

6. Replace the following:
SET Identity_insert products2 on
INSERT into products2 (ID, product) SELECT * from product

Implementation passed.

Summarize:

1. At any one time in each connection session, only one table can be set IDENTITY_INSERT on, and the setting is only valid for the current session;
2. In the insert operation on the identity column, be sure to list this identity column (and, of course, to list the related other columns as well).

You can specify an explicit value for an identity column in a table only if a list of columns is used and IDENTITY_INSERT is on