SQL Server 2005 OUTPUT 子句完整實戰：INSERT/UPDATE/DELETE 同步取得受影響資料

一、 OUTPUT 子句核心價值

SQL Server 2005 引入 OUTPUT 子句，讓 DML 陳述式（INSERT/UPDATE/DELETE）**在同一個原子操作中**回傳受影響的資料列（含身分識別欄位、計算欄位、預設值、觸發器修改後的值），免去 SCOPE_IDENTITY()、@@IDENTITY、觸發器、暫存表等繞彎做法。

二、 語法結構

-- INSERT
INSERT target_table (cols) 
OUTPUT inserted.col1, inserted.col2 INTO @TableVar
SELECT ... FROM source;

-- UPDATE
UPDATE target_table 
SET col = value
OUTPUT inserted.*, deleted.* INTO @TableVar
WHERE ...;

-- DELETE
DELETE target_table
OUTPUT deleted.* INTO @TableVar
WHERE ...;

• inserted：新值（INSERT/UPDATE）
• deleted：舊值（UPDATE/DELETE）
• INTO @TableVar：導向表變數/實體表（選用，省略則直接回傳用戶端）

三、 完整實戰範例

準備資料

IF OBJECT_ID('ta') IS NOT NULL DROP TABLE ta;
CREATE TABLE ta (ID INT IDENTITY, [name] VARCHAR(10));
INSERT ta([name]) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g');

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb;
CREATE TABLE tb (ID INT IDENTITY, [name] VARCHAR(10));
INSERT tb([name]) VALUES ('a'),('b'),('c');

1. INSERT + OUTPUT INTO 表變數（同步取得新身分識別值）

DECLARE @t TABLE (ID INT, [name] VARCHAR(10));

INSERT tb 
OUTPUT inserted.ID, inserted.[name] INTO @t
SELECT [name] FROM ta 
WHERE NOT EXISTS (SELECT 1 FROM tb WHERE [name] = ta.[name]);

SELECT * FROM @t;
-- 結果: ID=4~7, name=d,e,f,g

2. DELETE + OUTPUT 直接回傳用戶端

DELETE tb OUTPUT deleted.* WHERE ID = 9;
-- 直接在結果格網顯示被刪除的列

3. UPDATE + OUTPUT 同時取得舊/新值

UPDATE tb SET [name] = 'test' 
OUTPUT inserted.*, deleted.* 
WHERE ID = 10;
-- inserted: 新值 (10, 'test')
-- deleted:  舊值 (10, 'f')

四、 進階應用模式

4.1 合併同步（UPSERT）並記錄變更

MERGE tb AS target
USING (SELECT [name] FROM ta) AS src
ON target.[name] = src.[name]
WHEN NOT MATCHED THEN
    INSERT ([name]) VALUES (src.[name])
WHEN MATCHED THEN
    UPDATE SET target.[name] = src.[name] -- 示意
OUTPUT $action, inserted.*, deleted.*;

4.2 搭配 TRY...CATCH 進行稽核記錄

BEGIN TRY
    BEGIN TRAN;
    DELETE tb OUTPUT deleted.ID, deleted.[name], GETDATE(), 'DELETE' 
          INTO AuditLog (EntityID, EntityName, ChangeTime, Operation)
    WHERE [name] > 'c';
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK; THROW;
END CATCH

五、 限制與注意事項

六、 效能優勢

• 單次往返、單一交易、單一執行計畫完成「修改 + 取得結果」。
• 避免額外 SELECT SCOPE_IDENTITY() 或 SELECT * FROM table WHERE ... 的二次掃描。
• 表變數 @t 位於記憶體（小量時），寫入/讀取極快。

七、 總結

OUTPUT 子句是 SQL Server 2005 最實用的 T-SQL 增強之一，**所有需要「寫入並立即取得結果」的場景都應優先考慮**；搭配表變數、MERGE、稽核記錄，能大幅簡化程式碼並提升並發效能。

1. 上一篇文章： T-SQL 分隔字串第 N 片段更新：單一陳述式實作字串分段修改
2. 下一篇文章： SQL Server 2005 大量資料分頁查詢最佳實踐

SQL Server 2005

SQL Server 2005 PIVOT/UNPIVOT 行列轉換實戰：從儲存模型到報表呈現

核心場景 學生選課成績系統：課程數量不固定，資料表採用**縱

SQL Server 2005 與 2000 雙向資料遷移四大法：分離附加、備份還原、匯入匯出、指令碼

方向限制說明 2000 → 2005： 全部四法皆可。 2

SQL Server TRUNCATE TABLE 交易回滾實測：在交易中可回滾、外部不可回滾

迷思釐清 許多人以為 TRUNCATE TABLE 不可回滾

SQL Server 2005 身分識別欄位 (自動編號) 完整設定指南

身分識別欄位 讓資料庫自動產生連續唯一值 (如流水號、主鍵)