使用 MERGE 在单个语句中对表执行 INSERT 和 UPDATE 操作
程序员文章站
2022-05-26 20:20:32
...
一种常见的应用场景是在存在匹配行时更新表中的一个或多个列,或在不存在匹配行时将数据作为新行插入。 此操作一般通过将参数传递给包含相应 UPDATE 和 INSERT 语句的存储过程来执行。 借助 MERGE 语句,将可以在单个语句中一起执行这两项任务。 下面的示例
一种常见的应用场景是在存在匹配行时更新表中的一个或多个列,或在不存在匹配行时将数据作为新行插入。 此操作一般通过将参数传递给包含相应 UPDATE 和 INSERT 语句的存储过程来执行。 借助 MERGE 语句,将可以在单个语句中一起执行这两项任务。 下面的示例显示了一个同时包含 INSERT 语句和 UPDATE 语句的存储过程。 随后,此示例对该过程进行了修改,以使用单个 MERGE 语句执行等效的操作。USE AdventureWorks2012; GO CREATE 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) END END; GO -- Test the procedure and return the results. EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value'; SELECT UnitMeasureCode, Name FROM Production.UnitMeasure WHERE 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 ); GO ALTER 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