摘自:https://www.cnblogs.com/2019-11-11/p/11842089.html
merge集插入,更新,删除于一体,如果要对一个表同时进行插入,更新,删除2个或3三个操作。效率要高于单个操作。
merge into tableb b --被操作表
using (select id,servicepromotor,servicecmclient from tablea where servicepromotor>0) a--参照表 支持子查询
on a.id=b.client --关联关系
when matched --a,b表均匹配到做update
then update set b.servicepromotor=case when a.servicepromotor<>b.servicepromotor then a.servicepromotor else b.servicepromotor end
when not matched--a表有 b表匹配不到做insert
then insert(importdate,client,servicepromotor,serviceretailer,updatetime) values(getdate(),id,servicepromotor,0,getdate())
when not matched by source--a表没有做delete
then delete ;