Learning from SET IDENTITY_INSERT

Small glitch Error

 

Weird

Http://hi.baidu.com/ebiyuan/blog/item/6b511987c030fe2bc75cc360.html

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

Execution permission is granted by defaultSysadminFixed server roles andDb_ownerAndDb_ddladminFixed database roles and object owners.

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'GOSELECT * FROM productsGO-- Attempt to insert 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 products ONGO-- Attempt to insert an explicit ID value of 3INSERT INTO products (id, product) VALUES(3, 'garden shovel').GOSELECT * FROM productsGO-- Drop products table.DROP TABLE productsGO