SET IDENTITY_INSERT的用法

 SET IDENTITY_INSERT的用法
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }
--強制自增欄位可以進行插入記錄。
--Database是數據庫名稱
--Owner是表的所有名稱
--Table一個唯一字段的表名
Examples:
-- 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
select * from products
-- Create a gap in the identity values. 增除數據留縫隙
DELETE products
WHERE product = 'saw'
GO
SELECT *
FROM products
--此时id=3的这条记录不存在，被删除。
GO
-- Attempt to insert an explicit ID value of 3;  
-- should return a warning.   此時插入數據會出錯。被中斷
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
--提示：Cannot insert explicit value for identity column in table 'products' when IDENTITY_INSERT is set to OFF.
--当IDENTITY_INSERT 设置 OFF时，不能在表products里明确(也就是具体的自增自段值)的插入自增数据.
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.
SET IDENTITY_INSERT products OFF
DROP TABLE products
GO