SQL Server 2005 OUTPUT 子句實作：插入資料同步取回自動產生的 ID 值

SQL Server 2005 引入 OUTPUT 子句，允許在 INSERT、UPDATE、DELETE、MERGE 陳述式中直接存取 inserted、deleted 虛擬資料表，無需額外查詢即可取得身分識別欄位、預設值、計算欄位等伺服器端產生的值。本文以 UNIQUEIDENTIFIER + NEWID() 為例示範。

基本語法

INSERT INTO target_table (columns...)
OUTPUT inserted.column_list
VALUES (...)
-- 或
INSERT INTO target_table (columns...)
OUTPUT inserted.column_list
SELECT ... FROM source_table

範例：批次插入並取回 NEWID() 產生的 GUID

DECLARE @t TABLE (
    id UNIQUEIDENTIFIER DEFAULT NEWID(),
    name VARCHAR(100)
);

INSERT INTO @t (name)
OUTPUT inserted.id
SELECT 'jinjazz' UNION ALL SELECT 'sqlserver';

執行結果

id
------------------------------------
3880DB77-5ADF-472D-A68D-7518DD76F3B8
F478A3F5-A501-404E-A328-372EEC26279D

進階應用場景

1. 同時取回多個伺服器端產生欄位

DECLARE @Orders TABLE (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    OrderGUID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
    OrderDate DATETIME DEFAULT GETDATE(),
    CustomerID INT,
    Amount MONEY
);

INSERT INTO @Orders (CustomerID, Amount)
OUTPUT inserted.OrderID, inserted.OrderGUID, inserted.OrderDate
VALUES (1001, 1250.00), (1002, 890.50), (1003, 2100.00);

2. 將 OUTPUT 結果導入另一張表 (稽核/記錄)

DECLARE @Audit TABLE (OrderID INT, OrderGUID UNIQUEIDENTIFIER, ActionTime DATETIME);

INSERT INTO @Orders (CustomerID, Amount)
OUTPUT inserted.OrderID, inserted.OrderGUID, GETDATE() INTO @Audit
VALUES (1004, 500.00);

3. UPDATE 時取得舊值與新值

UPDATE @Orders
SET Amount = Amount * 1.1
OUTPUT deleted.OrderID, deleted.Amount AS OldAmount, inserted.Amount AS NewAmount
WHERE CustomerID = 1001;

4. DELETE 時保留被刪除資料

DELETE FROM @Orders
OUTPUT deleted.* INTO @DeletedOrdersArchive
WHERE OrderDate < '2020-01-01';

OUTPUT vs SCOPE_IDENTITY() / @@IDENTITY 比較

注意事項與限制

• OUTPUT 不支援參考 text、ntext、image、xml (大型值型別) 欄位，需改用 OUTPUT ... INTO 搭配暫存表
• 含 OUTPUT 的 DML 陳述式不可用於 VIEW (除非搭配 INSTEAD OF 觸發器)
• 平行執行計畫下，OUTPUT 結果順序不保證與輸入順序一致，需自行加 ROW_NUMBER() 對應
• 記憶體最佳化資料表 (In-Memory OLTP) 有特定限制，請查閱版本文件

最佳實務建議

-- 1. 習慣用 OUTPUT 取代 SCOPE_IDENTITY()，尤其批次作業
-- 2. 稽核欄位直接用 OUTPUT INTO 寫入稽核表，避免觸發器效能負擔
-- 3. MERGE 陳述式搭配 OUTPUT $action, inserted.*, deleted.* 可完整記錄增刪改
-- 4. 搭配 TRY...CATCH 確保交易一致性