|
下面介绍一下把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'; |