SQL Server 2005 OUTPUT 子句捕獲 INSERT 身分識值實戰：多表同步寫入的高效模式

前言：傳統捕獲 Identity 值的痛點

在 `INSERT ... SELECT` 批次寫入多筆資料時，傳統方法面臨困境：

• `@@IDENTITY` / `SCOPE_IDENTITY()` / `IDENT_CURRENT()` 僅回傳**單一值**，無法對應多筆插入。
• 游標/迴圈逐筆 `INSERT` → 效能極差、鎖爭用劇增。
• 暫存表 + `IDENTITY_INSERT ON` → 需修改目標表結構、維護複雜。

**OUTPUT 子句** 完美解決：單一陳述式批次插入，**同步回傳所有生成的 Identity 值** (含計算欄、預設值、Timestamp)。

實戰場景：產品主檔同步 (Upsert 模式)

需求： 供應商提供完整產品清單 (`ProductsToInsert`)，需將**不存在**的產品寫入主檔 (`Product`)，並取得新產品的 `ProductID` 以同步寫入多個關聯表 (庫存、價格、屬性)。

環境建立腳本 (TempDB 測試)

USE tempdb;
GO

-- 目標主檔：含 Identity 主鍵
IF OBJECT_ID('dbo.Product') IS NOT NULL DROP TABLE dbo.Product;
CREATE TABLE dbo.Product (
    ProductID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [Name] NVARCHAR(50) NOT NULL,
    ProductNumber NVARCHAR(25) NOT NULL,
    ListPrice MONEY NOT NULL
);
CREATE UNIQUE INDEX IX_Product_ProductNumber ON dbo.Product(ProductNumber);
GO

-- 來源暫存表：含待同步資料與回寫 Identity 欄位
IF OBJECT_ID('dbo.ProductsToInsert') IS NOT NULL DROP TABLE dbo.ProductsToInsert;
CREATE TABLE dbo.ProductsToInsert (
    RowID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [Name] NVARCHAR(50) NOT NULL,
    ProductNumber NVARCHAR(25) NOT NULL,
    ListPrice MONEY NOT NULL,
    InsertedIdentityValue INT NULL  -- 回寫欄位
);
GO

-- 模擬現有資料 (AdventureWorks 產品前 450 筆)
INSERT dbo.Product ([Name], ProductNumber, ListPrice)
SELECT TOP 450 [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product
ORDER BY SellStartDate, ProductID;
GO

-- 模擬來源資料 (含既有 + 新增)
INSERT dbo.ProductsToInsert ([Name], ProductNumber, ListPrice)
SELECT [Name], ProductNumber, ListPrice
FROM AdventureWorks.Production.Product;
GO

核心解決方案：OUTPUT INTO + MERGE/UPSERT 模式

-- 1. 宣告表變數接收 OUTPUT 結果
DECLARE @NewProducts TABLE (
    ProductID INT,
    ProductNumber NVARCHAR(25),
    [Name] NVARCHAR(50)
);

-- 2. MERGE 同步插入新產品，OUTPUT 捕獲 Identity
MERGE dbo.Product AS Target
USING (
    SELECT [Name], ProductNumber, ListPrice
    FROM dbo.ProductsToInsert
) AS Source
ON Target.ProductNumber = Source.ProductNumber
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Name], ProductNumber, ListPrice)
    VALUES (Source.[Name], Source.ProductNumber, Source.ListPrice)
OUTPUT inserted.ProductID, inserted.ProductNumber, inserted.[Name] INTO @NewProducts;

-- 3. 將捕獲的 Identity 回寫來源暫存表 (供後續同步關聯表使用)
UPDATE pti
SET InsertedIdentityValue = np.ProductID
FROM dbo.ProductsToInsert pti
JOIN @NewProducts np ON pti.ProductNumber = np.ProductNumber;

-- 4. 後續同步寫入關聯表 (範例：庫存表)
-- INSERT dbo.Inventory (ProductID, Qty)
-- SELECT ProductID, 0 FROM @NewProducts;

關鍵技術點解析

擴充模式：同時捕獲 INSERT/UPDATE/DELETE (稽核/同步/ETL)

MERGE dbo.TargetTable AS Target
USING dbo.SourceTable AS Source
ON Target.Key = Source.Key
WHEN MATCHED AND (Target.Val <> Source.Val) THEN
    UPDATE SET Target.Val = Source.Val
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Key, Val) VALUES (Source.Key, Source.Val)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT
    $action AS ActionType,
    inserted.Key AS NewKey, inserted.Val AS NewVal,
    deleted.Key AS OldKey, deleted.Val AS OldVal
INTO @SyncAudit;

• `$action` 回傳 `'INSERT'`/`'UPDATE'`/`'DELETE'`，完整記錄異動類型。
• `inserted`/`deleted` 虛擬表同 `TRIGGER`，提供新舊值對照。

避坑指南

• OUTPUT INTO 目標不能參與複寫/異動追蹤/資料行集：改用暫存表 `#Temp`。
• 觸發器內部使用 OUTPUT 受限：直接操作 `inserted`/`deleted` 表。
• 大量資料 (＞10萬列) 表變數無統計資料可能導致計畫劣化：改用 `#Temp` + `CREATE INDEX`。
• Identity 種子/增量衝突：合併多來源時注意 `DBCC CHECKIDENT` 重新播種。

結語

`MERGE + OUTPUT INTO` 是 SQL Server 2008+ 實現 **高效批次 Upsert + Identity 捕獲 + 多表同步** 的黃金組合。將「插入、取值、回寫、同步」壓縮為單一批次的原子操作，消除游標迴圈、暫存表維護、競爭條件，是現代 ETL、資料同步、主資料管理 (MDM) 管線的核心模式。