MERGEのテスト

CreateTable.sql

create table MergeTest(
  Id int unique not null,
  Memo nvarchar(200)
);

insert into MergeTest values(1, 'test1')
insert into MergeTest values(2, 'test2')
insert into MergeTest values(3, 'test3')
insert into MergeTest values(4, 'test4')

Merge1.sql

declare @id int
declare @memo nvarchar(200)

set @id = 4;
set @memo = 'change';

merge MergeTest as target
using (select @id, @memo) as source(Id, Memo)
  on (target.Id = source.Id)
when matched then
  update set target.Memo = source.Memo
when not matched then
  insert (Id, Memo) values(source.Id, source.Memo);

Merge2.sql

merge MergeTest as target
using (values(4, 'change2'), (5, 'insert')) as source(Id, Memo)
  on (target.Id = source.Id)
when matched then
  update set target.Memo = source.Memo
when not matched then
  insert (Id, Memo) values(source.Id, source.Memo);