SQL Server 2005 OUTPUT 子句實戰：DML 操作即時回傳資料

SQL Server 2005 引入 OUTPUT 子句，讓 INSERT、UPDATE、DELETE 陳述式能直接將異動前後的資料回傳至資料表變數，無需觸發器即可實現稽核、歸檔、訊息處理等場景。

語法核心

OUTPUT <dml_select_list> INTO @table_variable

• INSERT：僅能存取 inserted 虛擬表（新資料）。
• DELETE：僅能存取 deleted 虛擬表（舊資料）。
• UPDATE：同時存取 inserted（新值）與 deleted（舊值）。

案例 1：大量歷史資料分批歸檔

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

WHILE 1=1
BEGIN
    BEGIN TRAN
    DELETE TOP(5000) FROM Orders
    OUTPUT deleted.* INTO @DeletedOrders
    WHERE orderdate < '20030101';

    INSERT INTO OrdersArchive SELECT * FROM @DeletedOrders;
    COMMIT TRAN;

    DELETE FROM @DeletedOrders;
    IF @@ROWCOUNT < 5000 BREAK;
END

要點：OUTPUT deleted.* 將刪除的資料即時寫入表變數，再批次寫入歸檔表，避免單一大型交易導致記錄檔暴漲。

案例 2：存儲程序中同步回傳新增資料

CREATE TABLE Employee (
    EmpID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(100), LastName VARCHAR(100), Sex CHAR(1),
    Salutation AS CASE WHEN Sex='M' THEN 'Mr.' WHEN Sex='F' THEN 'Ms.' ELSE '' END,
    Salary MONEY,
    Deductions AS CASE 
        WHEN Salary < 3000 THEN 0.20*Salary
        WHEN Salary BETWEEN 30000 AND 50000 THEN 0.26*Salary
        WHEN Salary BETWEEN 50001 AND 75000 THEN 0.30*Salary
        ELSE 0.35*Salary END,
    DOJ DATETIME DEFAULT GETDATE()
);

CREATE PROCEDURE employee_insert
    @Fname VARCHAR(100), @Lname VARCHAR(100), @sex CHAR(1), @Salary MONEY
AS
BEGIN
    DECLARE @empoutput TABLE (
        EmpID INT, FirstName VARCHAR(100), LastName VARCHAR(100),
        Sex CHAR(1), Salutation CHAR(3), Salary MONEY, Deductions MONEY, DOJ DATETIME
    );

    INSERT INTO Employee (FirstName, LastName, Salary, Sex)
    OUTPUT inserted.* INTO @empoutput
    VALUES (@Fname, @Lname, @Salary, @sex);

    SELECT * FROM @empoutput;
END

呼叫 EXEC employee_insert 'Miho','Yoshikawa','F',146000 即可同步取得含計算欄位的完整新增記錄。

效能與應用優勢

• 單一陳述式完成異動與回傳，減少往返網路延遲。
• 避免觸發器額外負擔，邏輯集中於應用層。
• 配合 TOP (n) 分批處理，控制交易大小與記錄檔增長。
• 適用於 ETL 清洗、稽核軌跡、即時通知等場景。

OUTPUT 子句是 SQL Server 2005 重要的集合導向增強功能，善用它能大幅簡化資料異動後的後續處理邏輯。