sql server 中使用merge 语法

sql server 中自2008后引入merge 关键字，为学习使用，下面写个小脚本

use Test;
-- 创建表
create table t1(
id int,
score int
)

create table t2(
id int,
name varchar(24),
score int
)

--2 插入测试数据
insert into t1
values(1,23),(2,34),(3,235425)

insert into t2
values(1,'name1',225),(2,'name2',225),(3,'name3',225),(4,'name4',225)
--3 使用merge ，有相同记录更新，不存在 插入新记录
merge into t1
using t2 on t1.id = t2.id
when matched then
update set t1.score = t2.score
when not matched then
insert (id,score)
values(t2.id,t2.score);

-- select * from t1

 

=============merge into 支持以下几种方式===

1) matched 和not matched clauses 同时使用
   merge into acct a
     using subs b on (a.msid=b.msid)    
   when MATCHED then
        update set a.areacode=b.areacode
        where b.ms_type=0
   when NOT MATCHED then
        insert(msid,bill_month,areacode)
        values(b.msid,'200702',b.areacode)
        where b.ms_type=0;
2) 只有not matched clause,也就是只插入不更新
   merge into acct a
     using subs b on (a.msid=b.msid)  
   when NOT MATCHED then
        insert(msid,bill_month,areacode)
        values(b.msid,'200702',b.areacode)
        where b.ms_type=0;

3) 只有matched clause, 也就是只更新不插入
   merge into acct a
     using subs b on (a.msid=b.msid)
   when MATCHED then
        update set a.areacode=b.areacode
        where b.ms_type=0;

4）matched时删除
merge into acct a
     using subs b on (a.msid=b.msid)
   when MATCHED then
        update set a.areacode=b.areacode       
        delete where (b.ms_type!=0);