T-sql merge to partial target table by where-clause
/* Example of how to merge partial set of target table
In this example I merge from #mysource to #mytarget, but only affect type='car'
*/
if object_id('tempdb..#mytarget') is not null
drop table #mytarget
if object_id('tempdb..#mysource') is not null
drop table #mysource
create table #mytarget
( id int not null,
type varchar(100) not null,
name varchar(100) not null )
create table #mysource
( id int not null,
type varchar(100) not null,
name varchar(100) not null )
insert #mytarget
values
(1,'car','volvo'),
(2,'car','bmw'),
(3,'car','saab'),
(4,'car','jeep'), --not existing in source (will be deleted)
(5,'animal','cat') --not existing in source (will be unaffected)
insert #mysource
values
(1,'car','volvo'),
(2,'car','bmw'),
(3,'car','sååb'), --name changed
(6,'car','opel') --new item of type car
begin tran
select 'Before merge' as message
select * from #mysource
select * from #mytarget
;with mergeTarget as
(
select *
from #mytarget mt
where mt.type in (select type from #mysource)
)
merge mergetarget as target
using
( select s.* from #mysource s
) as source
on target.Id=source.Id
when matched
and (target.name!=source.name
or target.type!=source.type)
then update set
name=source.name,
type=source.type
when not matched by target
then insert (id,name,type)
values (source.id,source.name,source.type)
when not matched by source
then delete
;
select 'After merge' as message
select * from #mysource
select * from #mytarget
--see that animal cat is unaffected!
rollback tran
In this example I merge from #mysource to #mytarget, but only affect type='car'
*/
if object_id('tempdb..#mytarget') is not null
drop table #mytarget
if object_id('tempdb..#mysource') is not null
drop table #mysource
create table #mytarget
( id int not null,
type varchar(100) not null,
name varchar(100) not null )
create table #mysource
( id int not null,
type varchar(100) not null,
name varchar(100) not null )
insert #mytarget
values
(1,'car','volvo'),
(2,'car','bmw'),
(3,'car','saab'),
(4,'car','jeep'), --not existing in source (will be deleted)
(5,'animal','cat') --not existing in source (will be unaffected)
insert #mysource
values
(1,'car','volvo'),
(2,'car','bmw'),
(3,'car','sååb'), --name changed
(6,'car','opel') --new item of type car
begin tran
select 'Before merge' as message
select * from #mysource
select * from #mytarget
;with mergeTarget as
(
select *
from #mytarget mt
where mt.type in (select type from #mysource)
)
merge mergetarget as target
using
( select s.* from #mysource s
) as source
on target.Id=source.Id
when matched
and (target.name!=source.name
or target.type!=source.type)
then update set
name=source.name,
type=source.type
when not matched by target
then insert (id,name,type)
values (source.id,source.name,source.type)
when not matched by source
then delete
;
select 'After merge' as message
select * from #mysource
select * from #mytarget
--see that animal cat is unaffected!
rollback tran
Comments