Instantly share code, notes, and snippets.

Embed
What would you like to do?
MERGEのテスト
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')
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);
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment