SQLServer 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)

复制代码 代码如下:

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;

复制代码 代码如下:

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';