SQL SET IDENTITY_INSERT

Source: Internet
Author: User
Tags one table

To insert a value to an automatic number (or ID column, IDENTITY), SET IDENTITY_INSERT

Example:

1. Create a table with an ID column:

The code is as follows: Copy code
Create table products (id int identity primary key, product varchar (40 ))

2. Try the following operations in the table:

The code is as follows: Copy code
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:

The code is as follows: Copy code
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:

The code is as follows: Copy code
Create table products2 (id int identity primary key, product varchar (40 ))

Then execute:

The code is as follows: Copy code
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:

The code is as follows: Copy code
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:

The code is as follows: Copy code
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:

The code is as follows: Copy code
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

The code is as follows: Copy code
SET IDENTITY_INSERT

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

Syntax

The code is as follows: Copy code
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.

The code is as follows: Copy code

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.