您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 OUTPUT 子句實戰：從 DML 直接回傳異動資料的高效模式

編輯：關於SqlServer

前言：OUTPUT 子句 —— DML 的「雙向通道」

SQL Server 2005 引入 OUTPUT 子句，打破傳統 DML (INSERT/UPDATE/DELETE) 僅回傳影響筆數的限制，允許在單一陳述式中將異動前後的資料影像直接導向資料表變數、暫存表或呼叫端應用程式。這為稽核追蹤、異步處理、資料封存、ETL 增量邏輯帶來革命性簡化。

語法核心與虛擬表

INSERT | UPDATE | DELETE ...
OUTPUT  INTO @table_variable | #temp_table
...

兩個神奇虛擬表

虛擬表	含義	適用 DML
inserted	新資料影像 (異動後)	INSERT, UPDATE
deleted	舊資料影像 (異動前)	UPDATE, DELETE

INSERT：僅能存取 inserted。
DELETE：僅能存取 deleted。
UPDATE：可同時存取 inserted (新值) 與 deleted (舊值)，極適合稽核欄位級異動。

經典實戰場景：大量歷史資料分批封存 (Purge & Archive)

情境： Orders 表達 TB 級，需定期將 2003 年前訂單移至 OrdersArchive，要求交易一致性、最小化鎖爭用、可中斷續傳。

解決方案：OUTPUT + 批次迴圈 + 交易控制

DECLARE @deletedOrders TABLE (
    orderid     INT,
    orderdate   DATETIME,
    empid       INT,
    custid      VARCHAR(5),
    qty         INT
);

WHILE 1 = 1
BEGIN
    BEGIN TRAN;

    -- 1. 批次刪除 5000 筆，並用 OUTPUT 將被刪列導向表變數
    DELETE TOP (5000) FROM Orders
    OUTPUT deleted.* INTO @deletedOrders
    WHERE orderdate < '20030101';

    -- 2. 將批次資料寫入封存表
    INSERT INTO OrdersArchive
    SELECT * FROM @deletedOrders;

    COMMIT TRAN;

    -- 3. 清空表變數供下一批次
    DELETE FROM @deletedOrders;

    -- 4. 終止條件：實際刪除筆數 < 批次大小，表示已處理完畢
    IF @@ROWCOUNT < 5000
        BREAK;
END

關鍵設計亮點

原子性批次： 每批 5000 筆在單一交易中完成 Delete+Insert，失敗自動回滾，無資料遺失/重複風險。
鎖升級控制： 小批次避免觸發表級鎖升級，並發影響最小化。
記憶體效率： 表變數 @deletedOrders 僅存在記憶體 (或 TempDB 溢寫)，批次間清空釋放，無持久化開銷。
可中斷續傳： 迴圈結構天然支援作業視窗控制，隨時停止下次從中斷點繼續。
無 Trigger 開銷： 取代傳統 AFTER DELETE Trigger 寫入封存表，避免 Trigger 巢狀、效能抖動、除錯困難。

進階應用模式

1. UPDATE 稽核欄位級異動

UPDATE dbo.Products
SET Price = Price * 1.1
OUTPUT inserted.ProductID, deleted.Price AS OldPrice, inserted.Price AS NewPrice, GETDATE() AS ChangeTime
INTO dbo.PriceAudit
WHERE CategoryID = 5;

2. INSERT 取得 Identity / Computed 欄位值 (替代 SCOPE_IDENTITY)

DECLARE @NewOrders TABLE (OrderID INT, OrderDate DATETIME, Total MONEY);
INSERT INTO Orders (CustomerID, OrderDate, Total)
OUTPUT inserted.OrderID, inserted.OrderDate, inserted.Total INTO @NewOrders
VALUES ('ALFKI', GETDATE(), 123.45);
-- @NewOrders 即包含完整新列，含 Identity、Default、Computed 欄位

3. MERGE 結合 OUTPUT 實現 UPSERT 稽核

MERGE INTO Target t
USING Source s ON t.Key = s.Key
WHEN MATCHED THEN UPDATE SET t.Val = s.Val
WHEN NOT MATCHED THEN INSERT (Key, Val) VALUES (s.Key, s.Val)
OUTPUT $action, inserted.*, deleted.* INTO @MergeAudit;
-- $action 回傳 'INSERT' / 'UPDATE' / 'DELETE'