CREATE TABLE TestTB (
ID INT IDENTITY(1,1) PRIMARY KEY,
Province NVARCHAR(50),
City NVARCHAR(50)
);
INSERT INTO TestTB (Province, City)
OUTPUT inserted.ID, inserted.Province, inserted.City
VALUES (N'廣東', N'深圳');
-- 結果:ID=1, Province=廣東, City=深圳
DELETE FROM TestTB
OUTPUT deleted.*
WHERE ID = 1;
-- 回傳完整被刪除行
UPDATE TestTB
SET Province = N'湖南', City = N'郴州'
OUTPUT
'更新前: ' + deleted.Province + deleted.City AS [Before],
'更新後: ' + inserted.Province + inserted.City AS [After]
WHERE ID = 1;
DECLARE @DeletedLog TABLE (
ID INT, Province NVARCHAR(50), City NVARCHAR(50), DeletedAt DATETIME DEFAULT GETDATE()
);
DELETE FROM TestTB
OUTPUT deleted.ID, deleted.Province, deleted.City, GETDATE()
INTO @DeletedLog
WHERE ID > 4;
SELECT * FROM @DeletedLog; -- 後續可 INSERT 正式審計表、發送通知等
UPDATE Products
SET Price = CASE WHEN Price > 100 THEN Price * 0.9 ELSE Price * 0.95 END
OUTPUT
inserted.ProductID,
deleted.Price AS OldPrice,
inserted.Price AS NewPrice,
CASE WHEN deleted.Price > 100 THEN '大額折扣' ELSE '小額折扣' END AS DiscountType
INTO PriceChangeLog
WHERE CategoryID = 3;
MERGE TargetTable AS T
USING SourceTable AS S ON T.ID = S.ID
WHEN MATCHED THEN UPDATE SET T.Val = S.Val
WHEN NOT MATCHED THEN INSERT (ID, Val) VALUES (S.ID, S.Val)
OUTPUT $action, inserted.*, deleted.* INTO MergeAuditLog;
-- $action: 'INSERT' / 'UPDATE' / 'DELETE'
OUTPUT INTO 目標表不可參與 MERGE/UPDATE/DELETE 同一陳述式OUTPUT 子句是 SQL Server 2005 引入的「資料變更可見化」利器,配合 inserted/deleted 虛擬表與 INTO 表變數/實體表,優雅解決審計、歸檔、同步、回傳新增 ID 等高頻需求,取代觸發器與多語句邏輯,提升效能與可維護性。