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);