SQLServer 2008 Merge话语的OUTPUT作用

发布时间：2023-07-31 15:03:36 所属栏目：MsSql教程来源：

导读：下面介绍一下把Output同2008的新T-sql语句Merge组合使用的方法：

新建下面表：

<div class="codetitle"><a style="CURSOR: pointer" data="35082" class="copybut" id="copybut35082" onclick="doCopy('

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

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!