SQL Server self-growing column inserts the specified value-SET identity_insert on| OFF

To insert a value into an AutoNumber (or identity column, identity), you need to set the SET Identity_insert

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

Report:
SQL Server Help documentation related content

SET Identity_insert

Allows explicit values to be inserted into the identity column of the table.

Grammar

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 that contains the identity column.

Comments

At any time, the Identity_insert property of only one table in a session can be set to ON. If a table has this property set to ON and a set IDENTITY_INSERT ON statement is issued for another table, Microsoft®sql Server™ returns an error message stating that set IDENTITY_INSERT is set to ON and reports This property has been set to on the table.

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.

Set IDENTITY_INSERT settings are set at execution or runtime, not at parse time.

Permissions

Execute permissions are granted by default to the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles, as well as the object owner.

Example

The following example creates a table with an identity column and shows how to use the SET IDENTITY_INSERT setting to populate the gaps in the identity values that are caused by the DELETE statement.

--Create Products table. CREATE table products (id int IDENTITY PRIMARY KEY, product varchar (max)) GO--Inserting values into product TABLE. Insert to Product values (' screwdriver ') insert into product values (' hammer ') insert into s (product) values (' saw ') INSERT into product values (' shovel ') GO – Create a gap in the identity VALUES. DELETE Products WHERE Product = ' saw ' Go SELECT * FROM products go--attempt to inserts an explicit ID value of 3; --should return a warning. INSERT into products (ID, product) VALUES (3, "garden Shovel") GO-SET identity_insert to ON. SET Identity_insert Product on GO – attempt to INSERT a explicit ID value of 3 INSERT into product (ID, products) value S (3, ' garden shovel '). Go SELECT * from the products Go – Drop Products table. DROP TABLE Products GO

Transferred from: http://hehongwei44.iteye.com/blog/1461654

SQL Server self-growing column inserts the specified value-SET identity_insert on| OFF (GO)