SET IDENTITY_INSERT 學習心得

想要將值插入到自動編號（或者說是識別欄位，IDENTITY）中去，需要設定　SET IDENTITY_INSERT

樣本：

1.首先建立一個有識別欄位的表：
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))

2.嘗試在表中做以下操作：
INSERT INTO products (id, product) VALUES(3, 'garden shovel')

結果會導致錯誤：“當 IDENTITY_INSERT 設定為 OFF 時，不能向表 'products' 中的識別欄位插入顯式值。”

3.改用：
SET IDENTITY_INSERT products ON
INSERT INTO products (id, product) VALUES(1, 'garden shovel')

返回正確。

4.建立另外一個表products2，嘗試相同插入操作：
CREATE TABLE products2 (id int IDENTITY PRIMARY KEY, product varchar(40))

然後執行：
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) VALUES(1, 'garden shovel')

導致錯誤：“表 'material.dbo.products' 的 IDENTITY_INSERT 已經為 ON。無法對錶 'products2' 執行 SET 操作。”

改為執行：
SET IDENTITY_INSERT products OFF
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) VALUES(2, 'garden shovel')

執行通過。

5.嘗試以下操作：
SET IDENTITY_INSERT products2 ON
INSERT INTO products2     SELECT * FROM products

導致錯誤：“僅當使用了列的列表，並且 IDENTITY_INSERT 為 ON 時，才能在表 'products2' 中為識別欄位指定顯式值。”

6.改為：
SET IDENTITY_INSERT products2 ON
INSERT INTO products2     (id, product)     SELECT * FROM products

執行通過。

總結：

1.每一次串連會話中的任一時刻，只能對一個表設定IDENTITY_INSERT ON，且設定只對當前會話有效；
2.在對識別欄位執行插入操作進，一定要列出此識別欄位（當然，同時也就需要列出相關的其他列了）。

附：
SQL　Server協助文檔相關內容

SET IDENTITY_INSERT
允許將顯式值插入表的識別欄位中。

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

參數
database

是指定的表所駐留的資料庫名稱。

owner

是表所有者的名稱。

table

是含有識別欄位的表名。

注釋
任何時候，會話中只有一個表的 IDENTITY_INSERT 屬性可以設定為 ON。如果某個表已將此屬性設定為 ON，並且為另一個表發出了 SET IDENTITY_INSERT ON 語句，則 Microsoft SQL Server 返回一個錯誤資訊，指出 SET IDENTITY_INSERT 已設定為 ON 並報告此屬性已設定為 ON 的表。

如果插入值大於表的當前標識值，則 SQL Server 自動將新插入值作為當前標識值使用。

SET IDENTITY_INSERT 的設定是在執行或運行時設定，而不是在分析時設定。

許可權
執行許可權預設授予 sysadmin 固定伺服器角色和 db_owner 及 db_ddladmin 固定資料庫角色以及對象所有者。

樣本
下例建立一個含有識別欄位的表，並顯示如何使用 SET IDENTITY_INSERT 設定填充由 DELETE 語句導致的標識值中的空隙。

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