Using the MERGE Statement to Perform an UPSERT

The term UPSERT refers to an operation that inserts rows into a table if they do not exist, otherwise they are updated.

Simple example:

USE tempdb;
GO
MERGE dbo.Product AS T  
USING dbo.NewInventory AS S 
ON T.ProductName = S.ProductName
WHEN MATCHED THEN  
  UPDATE SET T.Qty = T.Qty + S.Qty
WHEN NOT MATCHED THEN  
  INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty);
SELECT * FROM dbo.Product;

Applicable example:

MERGE example

Good article on the subject:

http://www.databasejournal.com/features/mssql/using-the-merge-statement-to-perform-an-upsert.html

Leave a Reply

Your email address will not be published. Required fields are marked *