行が存在すればupdate、存在しなければinsertするmerge文のサンプル

gistfile1.sql

use tempdb;


create table #Fruit(
	Name nvarchar(3),
	Price int,
	constraint PK_Fruit primary key(Name));
go

create proc #UpsertFruit
	@name nvarchar(3),
	@price int
as
	merge #Fruit as Target
	using (values(@name, @price)) as Source(Name, Price)
		on Target.Name = Source.Name
	when matched then
		update set Price = Source.Price
	when not matched then
		insert values(Source.Name, Source.Price);
go	


insert into #Fruit
values ('りんご', 150), ('みかん', 120), ('バナナ', 100);
select * from #Fruit order by Price;


exec #UpsertFruit @name = N'みかん', @price = 180;
select * from #Fruit order by Price;


exec #UpsertFruit @name = N'いちご', @price = 160;
select * from #Fruit order by Price;


drop table #Fruit;
drop proc #UpsertFruit;

http://ichiroku11.hatenablog.jp/entry/2013/04/15/234650