此文章主要讲述的是SQL Server 2008 Merge语句之功能，OUTPUT，SQL Server 2005的Output功能能把Insert，Update以及Delete的内容全部都返回，2008数据库中的Output同样具有此功能，下面介绍一下把Output同2008的新T-SQL语句Merge组合使用的方法：
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 Server 2008 Merge语句的OUTPUT功能的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';
以上的相关内容就是对SQL Server 2008 Merge语句的OUTPUT功能的介绍，望你能有所收获。
【编辑推荐】
