create table test1(col1 int,col2 varchar(100)) create table test2(col3 int,col4 varchar(100)) insert into test1 values(1,'
wang'
),(2,'
trieagle'
) insert into test2(col3) values(1),(2) merge test2 using test1 on test1.col1=test2.col3 when matched then update set col4=col2;
select * from test2
结果: col3 col4 1 wang 2 trieagle 二、进行进销存更新库存 Trade表为模拟进出库记录,正数表示入库,负数表示出库
create table stock(id int,qty int) create table trade(id int ,qty int) go insert into stock values (1,10),(2,20) insert into trade values(1,10),(1,-5),(1,20),(2,10),(2,-30),(3,5) merge stock using (select id,qty=sum(qty) from trade group by id) K on stock.id=k.id when matched and(stock.qty+k.qty)=0 then delete when matched then update set stock.qty=stock.qty+k.qty when not matched by target then insert values(k.id,k.qty);
select * from stock
结果: id qty 1 35 3 5 三、进行表之间数据的复制
drop table test1 drop table test2 create table test1(col1 int,col2 varchar(100)) create table test2(col3 int,col4 varchar(100)) merge test2 using test1 on test1.col1 =test2.col3 when matched and col2!=col4 then update set col4=col2 when not matched then insert values(col1,col2) when not matched by source then delete;
select* from test2
结果: col3 col4 1 wang 2 trieagle 继续:删掉test1中的一行,然后增加一行
Delete test1 where col1=1 Insert into test1 values(3,'
wyq'
)
然后再执行
merge test2 using test1 on test1.col1 =test2.col3 when matched and col2!=col4 then update set col4=col2 when not matched then insert values(col1,col2) when not matched by source then delete;
结果: col3 col4 2 trieagle 3 wyq