sqlserver：merge关键字用法

环境：

• window10 x64
• sqlserver 2008 r2

参照：
《SQL Server merge用法》

一、merge关键字有什么用？

先看官方文档解释：《MERGE (Transact-SQL)》

我来简单说下：

merge关键字是用来比较两张表的数据并根据比较的结果执行Insert、Update、Delete语句，并最终保证两张表的数据“一致性”。

二、使用merge语法将一张表数据合并到另外一张表

首先，准备一个数据库，新建以下两张表并插入数据：

CREATE TABLE category (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);

INSERT INTO category(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
    (2,'Comfort Bicycles',25000),
    (3,'Cruisers Bicycles',13000),
    (4,'Cyclocross Bicycles',10000);


CREATE TABLE category_staging (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);


INSERT INTO category_staging(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
    (3,'Cruisers Bicycles',13000),
    (4,'Cyclocross Bicycles',20000),
    (5,'Electric Bikes',10000),
    (6,'Mountain Bikes',10000);

准备的两张表的数据如下：

select * from category_staging
select * from category

执行下面的sql语句，将category_staging表的数据合并到category表中：

MERGE category t 
    USING category_staging s
ON (s.category_id = t.category_id)
WHEN MATCHED
    THEN UPDATE SET 
        t.category_name = s.category_name,
        t.amount = s.amount
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (category_id, category_name, amount)
         VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

执行后的效果如下：

可以看到，表 category_staging和表category的数据已保持一致。

三、使用merge语法实现新增或修改的功能

程序中保存数据的时候，通常是先判断数据是否存在，如果存在的话就执行更新，如果不存在就执行插入。现在，我们可以借助merge语法直接执行“保存”操作。
如下，先准备一张表：

create table test5(
	id int primary key,
	name varchar(50),
	addr varchar(200),
	birth datetime2
);
insert into test5 values(1,'小明','天明路','2000-11-12')

现在，假设我们要新增或修改小明的数据（新增是针对所有字段，修改是仅修改名称），那么我们可以像下面这么写：

merge test5 with(holdlock) as Target
using(Values(1,'张小明','天明路','2001-12-12')) as Source(id,name,addr,birth)
on Target.id=Source.id
when matched then update set name = Source.name
when not matched then insert (id,name,addr,birth) values(Source.id, Source.name, Source.addr,Source.birth);

按照上面的写法执行后，我们可以看到小明这条数据已经更新了，你也可以将小明这条数据删除掉再执行，看看是否执行了新增。

虽然上面的写法可以实现新增或修改的效果，但是为了防止sql注入，我们一般都是将数据进行参数化处理，下面的语句具有同样的保存效果并且将数据进行了参数化处理：

declare @id bigint=1,@name varchar(max)='张晓明',@addr varchar(max)='天明路',@birth varchar(max)='2001-12-12'
merge test5 with(holdlock) as Target
using(Values(@id,@name,@addr,@birth)) as Source(id,name,addr,birth)
on Target.id=Source.id
when matched then update set name = Source.name
when not matched then insert (id,name,addr,birth) values(Source.id, Source.name, Source.addr,Source.birth);