Solutions for INSERT OR UPDATE on SQL Server
検証されていないので,時間があれば改めてからにしよう.
begin tran if exists(select* fromtable with(updlock,serializable)where key= @key) begin update tableset ... where key= @key end else begin insert table(key,...) values (@key,...) end commit tran
or
begin tran if exists(select* fromtable with(updlock,serializable)where key= @key) begin update tableset ... where key= @key end else begin insert table(key,...) values (@key,...) end commit tran
or
begin tran update table with (serializable) set ... where kay = @key if @@rowcount = 0 begin insert table (key, ...) values (@key,..) end commit tran
MS Sql2008 introduces
merge
from the SQL:2003 standard:merge tablename WITH(HOLDLOCK) as target using (values ('new value', 'different value')) as source (field1, field2) on target.idfield = 7 when matched then update set field1 = source.field1, field2 = source.field2, ... when not matched then insert ( idfield, field1, field2, ... ) values ( 7, source.field1, source.field2, ... )
別途参照http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/