SQL Server 在含有主键自增的表中执行插入自定义的主键的查询操作

SQL Server 在含有主键自增的表中执行插入自定义的主键的查询操作

1. SQL 语法

   SET IDENTITY_INSERT [ database. [ owner. ] ] { table name } { ON | OFF }

2. 实例

   -- 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. (删除第三行造成一个Id的间隔)
   DELETE products 
   WHERE product = 'saw'
   GO
   
   SELECT * 
   FROM products
   GO
   
   -- Attempt to insert an explicit ID value of 3; (尝试插入ID为3的一列数据) 
   -- should return a warning. (数据库会返回一个错误) 
   INSERT INTO products (id, product) VALUES(3, 'garden shovel')
   GO
   
   -- SET IDENTITY_INSERT to ON. (设置该表的IDENTITY_INSERT 为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

3. 注意
   IDENTITY_INSERT 属性在整个数据库中只有一个，如果在一个表中使用完成后必须将其关闭。如果没有关闭的话在该数据库的其他表中执行此类操作则会出现错误（SET IDENTITY_INSERT ON 时会出现错误，不能正常打开），所以必须在使用完成后顺便将其关闭才能方便下次的调用。
   代码如下：

   -- SET IDENTITY_INSERT to ON. 
   SET IDENTITY_INSERT products ON
   
   -- you other insert operations
   INSERT INTO products (id, product) VALUES(3, 'garden shovel')
   ......
   
   -- SET IDENTITY_INSERT to ON. 
   SET IDENTITY_INSERT products OFF 
   GO