SQL Server IDENTITY_INSERT 解決「無法為識別欄位指定明確值」錯誤

錯誤情境

兩個結構相同的資料表（含 IDENTITY 欄位），嘗試跨資料庫/資料表複製資料：

INSERT INTO wzgl2004.dbo.kc SELECT * FROM wzgl2003.dbo.kc

報錯：

僅當使用了資料行清單，且 IDENTITY_INSERT 為 ON 時，才能在資料表 'wzgl2004.dbo.kc' 中為識別欄位指定明確值。

解決方案

-- 1. 開啟 IDENTITY_INSERT
SET IDENTITY_INSERT wzgl2004.dbo.kc ON;

-- 2. 明確指定資料行清單（必須包含識別欄位）
INSERT INTO wzgl2004.dbo.kc (Id, Name, ...其他欄位...)
SELECT Id, Name, ...其他欄位... FROM wzgl2003.dbo.kc;

-- 3. 關閉 IDENTITY_INSERT
SET IDENTITY_INSERT wzgl2004.dbo.kc OFF;

關鍵規則

• 必須指定資料行清單：不能使用 SELECT *
• 識別欄位必須在清單中：才能接收來源資料的明確值
• 同一階段僅能有一個資料表開啟 IDENTITY_INSERT：嘗試開啟第二個會報錯
• 插入值大於目前識別種子：SQL Server 會自動調整種子值為插入的最大值

語法參考

SET IDENTITY_INSERT [database.[schema.]]資料表名稱 {ON | OFF}

完整範例：跨資料庫同步含識別欄位資料表

-- 假設 SourceDB.dbo.Orders 和 TargetDB.dbo.Orders 結構相同
-- OrderID 為 IDENTITY(1,1)

SET IDENTITY_INSERT TargetDB.dbo.Orders ON;

INSERT INTO TargetDB.dbo.Orders (OrderID, CustomerID, OrderDate, Amount, Status)
SELECT OrderID, CustomerID, OrderDate, Amount, Status
FROM SourceDB.dbo.Orders
WHERE OrderDate >= '2024-01-01';

SET IDENTITY_INSERT TargetDB.dbo.Orders OFF;

-- 同步後檢查識別種子
DBCC CHECKIDENT('TargetDB.dbo.Orders', NORESEED);

常見誤區