[Reprinted] only when the column list is used and the identity_insert parameter is on

When identity_insert is set to off, explicit values cannot be inserted into the 'products' column of the table ."

Example:

1. Create a table with an ID column:
Create Table products (ID int identity primary key, product varchar (40 ))

2. Try the following operations in the table:
Insert into products (ID, product) values (3, 'garden shovel ')

The result will cause an error: "When identity_insert is set to off, explicit values cannot be inserted into the ID column in The 'products' table ."

3. Switch:
Set identity_insert products on
Insert into products (ID, product) values (1, 'garden shovel ')

The returned result is correct.

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 (ID, product) values (1, 'garden shovel ')

Cause error: "The identity_insert of the" material. DBO. Products "table is already on. You cannot perform the set operation on the table 'products2 ."

Run the following command:
Set identity_insert products off
Set identity_insert products2 on
Insert into products2 (ID, product) values (2, 'garden shovel ')

Execution passed.

5. Try the following operations:
Set identity_insert products2 on
Insert into products2 select * from products

Cause error: "Only when the column list is used and identity_insert is on can an explicit value be specified for the ID column in 'products2 'of the table ."

6. changed:
Set identity_insert products2 on
Insert into products2 (ID, product) Select * from products

Execution passed.

Summary:

1. At any time in each connection session, only identity_insert on can be set for one table, and the setting is only valid for the current session;
2. When you insert an ID column, make sure to list the ID column (of course, you also need to list other related columns ).