一种常见的应用场景是在存在匹配行时更新表中的一个或多个列，或在不存在匹配行时将数据作为新行插入。 此操作一般通过将参数传递给包含相应 update 和 insert 语句的存储过程来执行。 借助 merge 语句，将可以在单个语句中一起执行这两项任务。 下面的示例
一种常见的应用场景是在存在匹配行时更新表中的一个或多个列，或在不存在匹配行时将数据作为新行插入。 此操作一般通过将参数传递给包含相应 update 和 insert 语句的存储过程来执行。 借助 merge 语句，将可以在单个语句中一起执行这两项任务。 下面的示例显示了一个同时包含 insert 语句和 update 语句的存储过程。 随后，此示例对该过程进行了修改，以使用单个 merge 语句执行等效的操作。 use adventureworks2012;gocreate procedure dbo.insertunitmeasure @unitmeasurecode nchar(3), @name nvarchar(25)as begin set nocount on;-- update the row if it exists. update production.unitmeasure set name = @name where unitmeasurecode = @unitmeasurecode-- insert the row if the update statement failed. if (@@rowcount = 0 ) begin insert into production.unitmeasure (unitmeasurecode, name) values (@unitmeasurecode, @name) endend;go-- test the procedure and return the results.exec insertunitmeasure @unitmeasurecode = 'abc', @name = 'test value';select unitmeasurecode, name from production.unitmeasurewhere unitmeasurecode = 'abc';go-- rewrite the procedure to perform the same operations using the merge statement.-- create a temporary table to hold the updated or inserted values from the output clause.create table #mytemptable (existingcode nchar(3), existingname nvarchar(50), existingdate datetime, actiontaken nvarchar(10), newcode nchar(3), newname nvarchar(50), newdate datetime );goalter procedure dbo.insertunitmeasure @unitmeasurecode nchar(3), @name nvarchar(25)as begin set nocount on; merge production.unitmeasure as target using (select @unitmeasurecode, @name) as source (unitmeasurecode, name) on (target.unitmeasurecode = source.unitmeasurecode) when matched then update set name = source.name when not matched then insert (unitmeasurecode, name) values (source.unitmeasurecode, source.name) output deleted.*, $action, inserted.* into #mytemptable;end;go-- test the procedure and return the results.exec insertunitmeasure @unitmeasurecode = 'abc', @name = 'new test value';exec insertunitmeasure @unitmeasurecode = 'xyz', @name = 'test value';exec insertunitmeasure @unitmeasurecode = 'abc', @name = 'another test value';select * from #mytemptable;-- cleanup delete from production.unitmeasure where unitmeasurecode in ('abc','xyz');drop table #mytemptable;go