SQL Server 2008 Merge语句的OUTPUT功能

 SQL Server 2005中的Output功能可以把Insert，Update和Delete的内容都返回，2008中的Output同样具有此功能，下面介绍一下把Output同2008的新T-SQL语句Merge组合使用的方法：

新建下面表：

view plaincopy to clipboardprint?
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) 
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语句为
view plaincopy to clipboardprint?
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; 
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情况。如果只需要其中一种，可以用下面这种方法过滤：

view plaincopy to clipboardprint?
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'; 

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/tjvictor/archive/2009/07/13/4344949.aspx