SQL Server 2008 MERGE OUTPUT 子句進階應用：稽核異動、條件過濾

情境：書本資料與每週異動表同步，寫入歷史稽核表

CREATE TABLE Book (
    ISBN varchar(20) PRIMARY KEY, Price decimal, Shelf int
);
CREATE TABLE WeeklyChange (
    ISBN varchar(20) PRIMARY KEY, Price decimal, Shelf int
);
CREATE TABLE BookHistory (
    Action nvarchar(10), NewISBN varchar(20), NewPrice decimal, NewShelf int,
    OldISBN varchar(20), OldPrice decimal, OldShelf int, ArchivedAt datetime2
); GO

基本 MERGE + OUTPUT 寫入稽核表

MERGE Book AS B
USING WeeklyChange AS WC ON B.ISBN = WC.ISBN
WHEN MATCHED AND (B.Price <> WC.Price OR B.Shelf <> WC.Shelf) THEN
    UPDATE SET B.Price = WC.Price, B.Shelf = WC.Shelf
WHEN NOT MATCHED THEN
    INSERT VALUES (WC.ISBN, WC.Price, WC.Shelf)
OUTPUT $action, inserted.*, deleted.*, SYSDATETIME() INTO BookHistory; GO

SELECT * FROM BookHistory; GO

Action NewISBN NewPrice NewShelf OldISBN OldPrice OldShelf ArchivedAt
------ ------- -------- -------- ------- -------- -------- ---------------------------
UPDATE A       101      1        A       100      1        2007-11-25 14:47:23.9907552
INSERT C       300      3        NULL    NULL     NULL     2007-11-25 14:47:23.9907552

進階：僅記錄特定動作 (如只記錄 UPDATE)

將 MERGE 包裹於子查詢，外層以 WHERE Action = 'UPDATE' 過濾：

INSERT INTO Book (ISBN, Price, Shelf, ArchivedAt)
SELECT ISBN, Price, Shelf, GETDATE()
FROM (
    MERGE Book AS B
    USING WeeklyChange AS WC
    ON B.ISBN = WC.ISBN AND B.ArchivedAt IS NULL
    WHEN MATCHED AND (B.Price <> WC.Price OR B.Shelf <> WC.Shelf) THEN
        UPDATE SET Price = WC.Price, Shelf = WC.Shelf
    WHEN NOT MATCHED THEN
        INSERT VALUES (WC.ISBN, WC.Price, WC.Shelf, NULL)
    OUTPUT $action, WC.ISBN, Deleted.Price, Deleted.Shelf
) CHANGES (Action, ISBN, Price, Shelf)
WHERE Action = 'UPDATE';

關鍵點

• $action 回傳 INSERT/UPDATE/DELETE，便於篩選。
• inserted.* 包含新值，deleted.* 包含舊值 (INSERT 時為 NULL)。
• OUTPUT 可導入資料表、表變數、或外層查詢進一步處理。