Using MERGE for SCD Type 2 with multiple updates

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”.