Learning from set identity_insert

To insert a value to an automatic number (or ID column, identity), set identity_insert

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 ).

Appendix:
SQL Server help documentation

Set identity_insert
Explicit values can be inserted into the table's ID column.

Syntax
Set identity_insert [database. [owner.] {table} {on | off}

Parameters
Database

Is the name of the database where the specified table resides.

Owner

Is the name of the table owner.

Table

Is the name of the table containing the ID column.

Note
At any time, the identity_insert attribute of only one table in the session can be set to on. If a table has set this attribute to on and sends a set identity_insert on statement to the other table, Microsoft SQL server returns an error message, indicates that set identity_insert has been set to on and reports that this attribute has been set to on.

If the inserted value is greater than the current table id value, SQL Server automatically uses the new value as the current ID value.

Set identity_insert is set during execution or running, rather than during analysis.

Permission
By default, the execution permission is granted to the SysAdmin fixed server role, db_owner, db_ddladmin fixed database role, and object owner.

Example
The following example creates a table with an ID column and shows how to use the set identity_insert setting to fill gaps in the ID values caused by the delete statement.

-- Create products table.
Create Table products (ID int identity primary key, product varchar (40 ))
Go
-- Inserting values into products table.
Insert into products (product) values ('screwdriver ')
Insert into products (product) values ('hammer ')
Insert into products (product) values ('saw ')
Insert into products (product) values ('shovel ')
Go

-- Create a gap in the identity values.
Delete Products
Where product = 'saw'
Go

Select *
From Products
Go

-- Attempt to insert an explicit id value of 3;
-- Shoshould return a warning.
Insert into products (ID, product) values (3, 'garden shovel ')
Go
-- Set identity_insert to on.
Set identity_insert products on
Go

-- Attempt to insert an explicit id value of 3
Insert into products (ID, product) values (3, 'garden shovel ').
Go

Select *
From Products
Go
-- Drop Products table.
Drop table Products
Go