SQL Server 2005 OUTPUT 子句實戰：INSERT/UPDATE/DELETE 返回受影響資料

SQL Server 2005 引入 OUTPUT 子句，可在 DML 陳述式中直接返回插入、刪除、更新前後的資料，無需觸發器或額外查詢，極大簡化業務邏輯與審計實作。

核心概念

• inserted 虛擬表：保存 INSERT/UPDATE 後的新資料。
• deleted 虛擬表：保存 DELETE/UPDATE 前的舊資料。
• 兩者結構與目標表相同，存在記憶體中，陳述式結束自動銷毀。

建立測試表

IF OBJECT_ID('dbo.TestTB') IS NOT NULL DROP TABLE dbo.TestTB;
CREATE TABLE dbo.TestTB (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Province VARCHAR(50),
    City VARCHAR(50)
);

場景一：INSERT 返回新增資料 (含身分識別值)

INSERT INTO dbo.TestTB (Province, City)
OUTPUT INSERTED.Province, INSERTED.City, INSERTED.ID  -- 可指定欄位或 INSERTED.*
VALUES ('廣東', '深圳');
-- 結果: 省份=廣東, 城市=深圳, ID=1

場景二：DELETE 返回被刪除資料

DELETE FROM dbo.TestTB
OUTPUT DELETED.*
WHERE ID = 1;
-- 結果: 完整被刪除列

場景三：UPDATE 同時返回更新前後資料 (審計神器)

UPDATE dbo.TestTB
SET Province = '湖南', City = '郴州'
OUTPUT '更新前:' + DELETED.Province + DELETED.City AS BeforeData,
       '更新後:' + INSERTED.Province + INSERTED.City AS AfterData
WHERE ID = 1;
-- 結果: BeforeData=更新前:廣東深圳, AfterData=更新後:湖南郴州

場景四：OUTPUT INTO 表變數/實體表 (異動同步、歷史表)

DECLARE @AuditLog TABLE (
    ID INT, Province VARCHAR(50), City VARCHAR(50), 
    ActionType VARCHAR(10), ChangeTime DATETIME DEFAULT GETDATE()
);

-- 刪除並記錄
DELETE FROM dbo.TestTB
OUTPUT DELETED.ID, DELETED.Province, DELETED.City, 'DELETE' INTO @AuditLog
WHERE ID > 4;

SELECT * FROM @AuditLog;

進階應用模式

限制與注意事項

• 不可對有觸發器的表使用 OUTPUT INTO 表變數 (可用實體表)。
• 含 FILESTREAM、text/ntext/image 欄位時有限制。
• OUTPUT 不支援 TOP 子句的不確定順序 (需加 ORDER BY 於子查詢)。
• 平行執行計畫下，OUTPUT 結果順序不保證與輸入順序一致。

善用 OUTPUT 子句，可將「異動執行」與「結果取得/審計記錄」合為原子操作，提升效能並簡化代碼，是 SQL Server 開發必備技能。