SQLServer 2008 Merge语句的OUTPUT功能
前端之家收集整理的这篇文章主要介绍了
SQLServer 2008 Merge语句的OUTPUT功能，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
下面介绍一下把Output同2008的新T-sql语句Merge组合使用的方法：
新建下面表：
<div class="codetitle"><a style="CURSOR: pointer" data="35082" class="copybut" id="copybut35082" onclick="doCopy('code35082')"> 代码如下:
<div class="codebody" id="code35082">
CREATE TABLE Book(
ISBN varchar(20) PRIMARY KEY,
Price decimal,
Shelf int) CREATE TABLE WeeklyChange(
ISBN varchar(20) PRIMARY KEY,
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语句为
<div class="codetitle">
<a style="CURSOR: pointer" data="26796" class="copybut" id="copybut26796" onclick="doCopy('code26796')"> 代码如下:
<div class="codebody" id="code26796">
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情况。如果只需要其中一种，可以用下面这种
方法过滤：
<div class="codetitle">
<a style="CURSOR: pointer" data="4770" class="copybut" id="copybut4770" onclick="doCopy('code4770')"> 代码如下:
<div class="codebody" id="code4770">
INSERT INTO Book(ISBN,Price,Shelf,ArchivedAt)
SELECT ISBN,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.Shelf,NULL)
OUTPUT $action,WC.ISBN,Deleted.Price,Deleted.Shelf
) CHANGES(Action,ISBN,Shelf)
WHERE Action = 'UPDATE';
