merge 用法

SQL中 Merge 函数用于更新表的数据：

Merge  a 

using  b  on(a.id = b.id)

when matched then 

update set name = b,name

when not matched then

insert a(id,name) values(b.id,b.name) 

 

--像 a 表更新数据，比照b表，如果id 一样的就把b表的name  覆盖过来 ；如果id不能匹配就把b中的行添加到a表中，晚上数据的采集和更新。

 

create table s1
(
    id int not null,
    name varchar(20),
    crdate datetime
)
insert into s1 (id ,name ,crdate) values(1,'Jan','2017-01-05')
insert into s1 (id ,name ,crdate) values(2,'jam','2017-02-04')
insert into s1 (id ,name ,crdate) values(3,'ken','2017-03-15') ----------2017-03-15
insert into s1 (id ,name ,crdate) values(4,'smith','2017-04-21')
insert into s1 (id ,name ,crdate) values(5,'johan','2017-05-15')

create table s2
(
    id int not null,
    name varchar(20),
    crdate datetime
)
insert into s2 (id ,name ,crdate) values(1,'Jan','2017-01-05')
insert into s2 (id ,name ,crdate) values(2,'jam','2017-02-04')
insert into s2 (id ,name ,crdate) values(3,'ken','2017-05-20')--2017-05-20   故意更改了时间
insert into s2 (id ,name ,crdate) values(6,'smith','2017-05-21') --    新增id6
insert into s2 (id ,name ,crdate) values(7,'johan','2017-09-15') --    新增id7


select * from s1
select * from s2
drop table s1
drop table s2

merge s1 as a
using s2 as b on(a.id = b.id)
when matched then 
update set name = b.name,crdate = b.crdate
when not matched then 
insert values (b.id,b.name,b.crdate);

b表也可以是直接（select * from ..）