欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

导入数据时, 存在的更新, 不存在的插入

程序员文章站 2022-06-09 18:52:29
...

导入数据时, 存在的更新, 不存在的插入 无 CREATE?trigger?tr_bank3?ON?bank3AFTER?INSERTAS??????Update?bank1?set?bank1.fmoney=bank1.fmoney+c.a???????From?????????(select?fid,sum(case?when?TYPE=0?then?fmoney?when?type=1?then?-fmoney?end)?as?a?Fr

导入数据时, 存在的更新, 不存在的插入
CREATE?trigger?tr_bank3?ON?bank3
AFTER?INSERT
AS
?
?
????Update?bank1?set?bank1.fmoney=bank1.fmoney+c.a?
??????From?
????????(select?fid,sum(case?when?TYPE=0?then?fmoney?when?type=1?then?-fmoney?end)?as?a?From?bank3?group?by?Fid)?C?
????where?
????????C.FID=bank1.FID?
????????AND?EXISTS(SELECT?1?FROM?INSERTED?WHERE?Fid=bank3.Fid))
?
????insert?into?bank1?
????select?
????????fid,
????????sum(case?when?TYPE=0?then?fmoney???when?type=1?then?-fmoney?end)?as?a?
????from?
????????bank3?AS?a
????WHERE?
????????EXISTS(SELECT?1?FROM?INSERTED?WHERE?Fid=a.Fid)
????AND?
????????NOT?EXISTS(SELECT?1?FROM?bank1?WHERE?Fid=a.Fid)
????group?by?Fid
MERGE [target] t
using [source] s on t.id = s.id
when matched then update t.name = s.name, t.age = s.age -- use "rowset1"
when not matched then insert values(id,name,age) -- use "rowset2"
when source not matched then delete; -- use "rowset3"