Using merge statement in SQL Server 2008

Using MERGE to perform INSERT and UPDATE operations on a table in a single statement.

A common scenario is updating one or more columns in a table if a matching row exists, or inserting the data as a new row if a matching row does not exist. This is usually done by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. 

With the MERGE statement, you can perform both tasks in a single statement. The following example shows a stored procedure that contains both an INSERT statement and an UPDATE statement. The procedure is then modified to perform the equivalent operations by using a single MERGE statement.

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. 

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.*; END; GO

 -- Test the procedure and return the results.

 EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value'; EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value'; -- Cleanup DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');