下面介紹一下把Output同2008的新T-SQL語句Merge組合使用的方法：
新建下面表：
復制代碼 代碼如下:
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)
SQL語句為
復制代碼 代碼如下:
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;
結果集為：
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
這里有Insert和Update兩種Output情況。如果只需要其中一種，可以用下面這種方法過濾：
復制代碼 代碼如下:
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';
您可能感興趣的文章:- SQLServer2008的實用小道具 merger使用介紹
- SQLServer 2008中SQL增強之三 Merge(在一條語句中使用Insert,Update,Delete)
- php函數array_merge用法一例(合并同類數組)
- SQLServer中merge函數用法詳解
