sql server merge

sql-server-merge.sql

create table source
(
  id int not null primary key identity (1,1),
  a varchar,
  b varchar
);

select * from source

create table target
(
  id int not null primary key identity (1,1),
  a varchar,
  b varchar
);

insert into source values ('1', '1');
insert into source values ('2', '3');
insert into source values ('3', '4');
insert into source values ('4', '5');


insert into target values ('1', NULL);
insert into target values ('2', NULL);
insert into target values ('3', NULL);
insert into target values ('4', NULL);

merge target t using (select a,b from source) as s(a,b) on (t.a = s.a)
  when matched then
    update set b = s.b
  when not matched then
    insert (a, b) values (s.a, s.b) 
  output deleted.*, inserted.* ;

  select * from target