Below is the typical MERGE statement for SCD Type 2:
insert into dest (a, b)
select a, b
from
(
merge dest as target
using src as source
on source.a = target.a
when not matched by target
then insert (a,b) values(a,b)
when matched
then update set target.a = source.a, target.b = source.b
output $Action as my_action
) mrg
where mrg.my_action = 'UPDATE'
;
Since a MERGE statement cannot update more than one match at a time, in scenarios which the source contains historical data that for each matching key (per ON statament) may need more than 1 action in a single merge statement run (e.g. one INSERT and multiple UPDATEs), above statement will not work properly.
As a work around you can rank the source for multiple singular actions and put merge statement in a loop. Then every INSERT/UPDATE action will run correctly per each MERGE run
select a,b, rownumber() over(partition by a order by b) as rank
into #src
from src
declare @counter int = 1
declare @max_counter int = (select max(rank) from #src)
while @counter <= @max_counter
begin
insert into dest (a, b)
select a, b
from
(
merge dest as target
using (select a,b, from #src where rank = @counter) as source
on source.a = target.a
when not matched by target
then insert (a,b) values(a,b)
when matched
then update set target.a = source.a, target.b = source.b
output $Action as my_action
) mrg
where mrg.my_action = 'UPDATE'
;
set @counter = @counter + 1
end
Note the bold snippet in above is replaced with the previous “src”.