SQL Server 2005 兩大實用新語法：OUTPUT INTO 與 表變量

SQL Server 2005 引入 OUTPUT 子句與增強型表變量，極大簡化了數據移動、審計、臨時結果集處理等常見場景。本文通過實戰示例演示核心用法。

一、OUTPUT ... INTO：單語句完成數據移動/審計

傳統做法：INSERT INTO Target SELECT * FROM Source; DELETE FROM Source; 兩步操作，事務一致性需額外控制。OUTPUT 讓刪除/插入/更新的「受影響行」直接導向目標表/表變量。

場景 1：歸檔刪除記錄（備份表）

DELETE FROM [TableUsing]
OUTPUT DELETED.* INTO [TableBak];  -- 刪除的所有列完整存入備份表

場景 2：移動時轉換字段值（如添加歸檔時間）

DELETE FROM [TableUsing]
OUTPUT DELETED.Field1, DELETED.Field2, GETDATE() AS ArchiveTime INTO [TableBak];

關鍵點

• DELETED / INSERTED 虛擬表分別保存刪除/插入前後的行映像。
• UPDATE 同時產生 DELETED (舊值) 和 INSERTED (新值)。
• 目標表 TableBak 需預先存在，結構兼容 OUTPUT 列清單。
• 單語句原子性，無需顯式事務。

二、表變量：高性能臨時結果集容器

語法：DECLARE @tmp TABLE (ID INT, City NVARCHAR(50));

優勢對比 #TempTable：

實戰示例：分組取最小 ID 彙總

DECLARE @tmp TABLE (ID INT, City NVARCHAR(50));

INSERT INTO @tmp
SELECT MIN(ID), City
FROM [TableTest] WITH (NOLOCK)
GROUP BY City;

-- 後續在批次內直接 JOIN @tmp 使用，無 tempdb 爭用
SELECT t.*, tmp.ID AS MinID
FROM [TableTest] t
JOIN @tmp tmp ON t.City = tmp.City;

進階組合：OUTPUT 導入表變量

DECLARE @DeletedRows TABLE (ID INT, UserName NVARCHAR(50), DeletedAt DATETIME);

DELETE FROM Users
OUTPUT DELETED.ID, DELETED.UserName, GETDATE() INTO @DeletedRows
WHERE LastLogin < DATEADD(YEAR, -1, GETDATE());

-- 批次內直接處理 @DeletedRows，如發送通知、寫入日志表
SELECT * FROM @DeletedRows;

避坑指南

1. 表變量無統計信息：大數據量 (>10k 行) JOIN 時優化器可能選擇差計劃。解決：OPTION (RECOMPILE) 或改用臨時表。
2. OUTPUT 不支持觸發器目標表：若目標表有 INSTEAD OF 觸發器，OUTPUT 子句會報錯。
3. WITH (NOLOCK) 僅適用讀取，OUTPUT 寫入目標表不受影響。
4. 記憶體壓力：表變量數據同樣存儲於 tempdb，超大集合會導致 tempdb 膨脹。

善用 OUTPUT INTO 消除「讀-寫」兩步操作，用表變量替代輕量級臨時表減少重編譯，是 SQL Server 2005+ 開發提升代碼質量與性能的關鍵技巧。

1. 上一篇文章： SQL Server 警報疑難排查完整手冊：從服務配置到性能計數器
2. 下一篇文章： SQL Server 2005 SP2 數據庫維護計劃實戰全指南

關於SqlServer

SQL Server 2000 向 2008 R2 異構複寫推送資料完整實戰教學

專案背景 來源端：SQL Server 2000 (Wind

SSIS 實戰系列 (2)：控制流元素全景解析

控制流三大支柱 SSIS 工具箱本質揭示：順序、選擇、迴圈（

SQL Server 2005 架構與用戶分離機制深度解析

從 SQL Server 2000 到 2005 的範式轉移

SQL Server 2000 連接埠修改圖解：伺服器/用戶端網路工具雙邊配置

SQL Server 2000 預設監聽 TCP 1433。