介绍
Merge关键字是一个神奇的DML关键字。它在SQL Server 2008被引入,它能将Insert,Update,Delete简单的并为一句。MSDN对于Merge的解释非常的短小精悍:”根据与源表联接的结果,对目标表执行插入、更新或删除操作。例如,根据在另一个表中找到的差异在一个表中插入、更新或删除行,可以对两个表进行同步。”,通过这个描述,我们可以看出Merge是关于对于两个表之间的数据进行操作的。可以想象出,需要使用Merge的场景比如:
- 数据同步
- 数据转换
- 基于源表对目标表做Insert,Update,Delete操作
语法
merge [into] [目标表]
using <源表>
on 条件
when matched 操作
when not matched 操作;
举个例子
Merge建立连个表t1,t2。
CREATE TABLE t1 (
id INT PRIMARY KEY,
name VARCHAR(20),
department VARCHAR(20),
grade INT DEFAULT 0
);
CREATE TABLE t2 (
id INT PRIMARY KEY,
name VARCHAR(20),
department VARCHAR(20),
grade INT DEFAULT 0
);
Merge插入测试数据
INSERT INTO dbo.t1
( id, name, department, grade )
VALUES ( 1, 'aaaa', 'd1', 60 ),
( 2, 'bbbb', 'd2', 70 ),
( 3, 'cccc', 'd2', 70 ),
( 4, 'dddd', 'd2', 70 ),
( 5, 'eeee', 'd2', 75 ),
( 6, 'ffff', 'd2', 70 ),
( 7, 'gggg', 'd2', 80 );
INSERT INTO dbo.t2
( id, name, department, grade )
VALUES ( 1, 'aaaa', 'd1', 60 ),
( 2, 'bbbb', 'd2', 70 ),
( 3, 'cccc', 'd3', 74 ),
( 4, 'dddd', 'd4', 77 ),
( 5, 'eeee', 'd5', 75 ),
( 6, 'ffff', 'd6', 70 ),
( 7, 'gggg', 'd7', 88 ),
( 8, 'hhhh', 'd8', 70 ),
( 9, 'iiii', 'd7', 70 ),
( 10, 'jjjj', 'd9', 70 ),
( 11, 'kkkk', 'd9', 70 );
用merge语句将t2表的数据更新到t1表,如果t1表里存在t2表有name字段相等的数据(当然匹配条件可以有多个),就用t2表的department字段值更新t1表的department值,如果t2表的name字段值在t1表里不存在就在t1表里插入新的行。
MERGE INTO dbo.t1
USING dbo.t2
ON dbo.t1.name = t2.name --匹配字段
WHEN MATCHED THEN UPDATE SET t1.department = t2.department --匹配有就更新
WHEN NOT MATCHED THEN INSERT VALUES(id,name,department,grade); --匹配没有就插入
Merge当然如果看不出都对t1表做了哪些操作,还可以借助OUTPUT关键字来查看变化,例如修改语句如下
MERGE INTO dbo.t1
USING dbo.t2
ON dbo.t1.name = t2.name
WHEN MATCHED THEN UPDATE SET t1.department = t2.department
WHEN NOT MATCHED THEN INSERT VALUES(id,name,department,grade)
OUTPUT --显示操作变化
$action AS 操作类型,
Inserted.id AS 插入id,
Inserted.name AS 插入name,
Inserted.department AS 插入department,
Inserted.grade AS 插入grade;
Merge执行结果如图