您现在的位置是：网站首页> 编程资料编程资料

SQLServer 2008 Merge语句的OUTPUT功能_mssql2008_

2023-05-27 507人已围观

简介 SQLServer 2008 Merge语句的OUTPUT功能_mssql2008_

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

您可能感兴趣的文章:

上一篇：SQL2008中阻止保存要求重新创建表的更改的解决方法_mssql2008_

下一篇：SQLServer 2008 新增T-SQL 简写语法_mssql2008_

您现在的位置是：网站首页> 编程资料编程资料

SQLServer 2008 Merge语句的OUTPUT功能_mssql2008_

相关内容

点击排行

本栏推荐

猜你喜欢