MERGE

What’s MERGE?

You could almost write a book about merge statements. But as real ninjas, we want to use the katana to split the subject cleanly. A MERGE statement performs insert, delete or update operations in a table within a statement. It can be used, for example, to synchronize tables, i.e. to insert the contents of a table into a second table if it does not already exist, to update if it already exists and to delete from the second table if it does not exist in the first table.

What you should keep in mind with the MERGE statement is that it uses a relatively complex syntax, and that it is not easy for others to tune such statements if you have a choice, so separate INSERT, UPDATE and DELETE statements are often the better choice.

How can you work with MERGE?

Actually, the use of MERGE statements is not that complicated, but we don’t know of any ninja who can reproduce them from the mind. Let’s start by creating data from which we want to update the database. We do this by creating a copy of the StockItem table:

SELECT [StockItemID]
      ,[StockItemName]
      ,[SupplierID]
      ,[ColorID]
      ,[UnitPackageID]
      ,[OuterPackageID]
      ,[Brand]
      ,[Size]
      ,[LeadTimeDays]
      ,[QuantityPerOuter]
      ,[IsChillerStock]
      ,[Barcode]
      ,[TaxRate]
      ,[UnitPrice] = [UnitPrice]+2.0
      ,[RecommendedRetailPrice] = [RecommendedRetailPrice]+2.0
      ,[TypicalWeightPerUnit]
      ,[MarketingComments]
      ,[InternalComments]
      ,[Photo]
      ,[CustomFields]
      ,[LastEditedBy]
  INTO [Warehouse].[StockItemUpdate] 
  FROM [Warehouse].[StockItems]
  WHERE StockItemID > 5

We have adjusted the prices and filtered out the items with the lowest StockItemIDs. So now we have a table with adjusted item prices which does not contain all items. Since the StockItems table contains foreign key relationships, which would make life difficult in this case, we make a copy of the table again:

SELECT [StockItemID]
      ,[StockItemName]
      ,[SupplierID]
      ,[ColorID]
      ,[UnitPackageID]
      ,[OuterPackageID]
      ,[Brand]
      ,[Size]
      ,[LeadTimeDays]
      ,[QuantityPerOuter]
      ,[IsChillerStock]
      ,[Barcode]
      ,[TaxRate]
      ,[UnitPrice]
      ,[RecommendedRetailPrice]
      ,[TypicalWeightPerUnit]
      ,[MarketingComments]
      ,[InternalComments]
      ,[Photo]
      ,[CustomFields]
      ,[LastEditedBy]
  INTO [Warehouse].[StockItemCopy] 
  FROM [Warehouse].[StockItems]

Now we want to synchronize the update table with the copy of the StockItem table. For this we use the MERGE statement. Basically it is structured as follows: It starts with a MERGE command, followed by the definition of the target and source and an ON condition how to find the matching rows in source and target. This is followed by one or more blocks that define what happens if the source and destination MATCH or not (although these blocks may also contain additional conditions). Concretely we proceed as follows: if we find matching rows, we update the prices (we can update all other columns here, of course), if the row is present in the source (i.e. the update table) but not in the target, then we insert it, if it is present in the target but not in the source, then we delete it from the target. The update table contained 222 rows out of 227 that were in the StockItems table (we finally filtered for the StockItemID. Now let’s execute the following MERGE statement:

MERGE [Warehouse].[StockitemCopy] AS tgt  
    USING (SELECT [StockItemID]
      ,[StockItemName]
      ,[SupplierID]
      ,[ColorID]
      ,[UnitPackageID]
      ,[OuterPackageID]
      ,[Brand]
      ,[Size]
      ,[LeadTimeDays]
      ,[QuantityPerOuter]
      ,[IsChillerStock]
      ,[Barcode]
      ,[TaxRate]
      ,[UnitPrice]
      ,[RecommendedRetailPrice]
      ,[TypicalWeightPerUnit]
      ,[MarketingComments]
      ,[InternalComments]
      ,[Photo]
      ,[CustomFields]
      ,[LastEditedBy] from [Warehouse].[StockItemUpdate]) AS src
    ON (tgt.[StockItemID] = src.[StockItemID])  
    WHEN MATCHED THEN
        UPDATE SET 
             [UnitPrice] = src.[UnitPrice]
            ,[RecommendedRetailPRice] = src.[RecommendedRetailPRice]
    WHEN NOT MATCHED BY TARGET THEN 
        INSERT ([StockItemID]
      ,[StockItemName]
      ,[SupplierID]
      ,[ColorID]
      ,[UnitPackageID]
      ,[OuterPackageID]
      ,[Brand]
      ,[Size]
      ,[LeadTimeDays]
      ,[QuantityPerOuter]
      ,[IsChillerStock]
      ,[Barcode]
      ,[TaxRate]
      ,[UnitPrice]
      ,[RecommendedRetailPrice]
      ,[TypicalWeightPerUnit]
      ,[MarketingComments]
      ,[InternalComments]
      ,[Photo]
      ,[CustomFields]
      ,[LastEditedBy])
      VALUES 
      (src.[StockItemID]
      ,src.[StockItemName]
      ,src.[SupplierID]
      ,src.[ColorID]
      ,src.[UnitPackageID]
      ,src.[OuterPackageID]
      ,src.[Brand]
      ,src.[Size]
      ,src.[LeadTimeDays]
      ,src.[QuantityPerOuter]
      ,src.[IsChillerStock]
      ,src.[Barcode]
      ,src.[TaxRate]
      ,src.[UnitPrice]
      ,src.[RecommendedRetailPrice]
      ,src.[TypicalWeightPerUnit]
      ,src.[MarketingComments]
      ,src.[InternalComments]
      ,src.[Photo]
      ,src.[CustomFields]
      ,src.[LastEditedBy]) 
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE;

If we now check the copy of the StockItems table, it contains exactly 222 rows, where the prices are all 2 Euro higher than in the original StockItems table.

References:

• Official documentation from Microsoft