Merge

Inserting, Updating, and Deleting Data by Using MERGE

In SQL Server 2008, you can perform insert, update, or delete operations in a single statement using the MERGE statement. The MERGE statement allows you to join a data source with a target table or view, and then perform multiple actions against the target based on the results of that join. For example, you can use the MERGE statement to perform the following operations:

• Conditionally insert or update rows in a target table.
  If the row exists in the target table, update one or more columns; otherwise, insert the data into a new row.
• Synchronize two tables.
  Insert, update, or delete rows in a target table based on differences with the source data.

The MERGE syntax consists of five primary clauses:

• The MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
• The USING clause specifies the data source being joined with the target.
• The ON clause specifies the join conditions that determine where the target and source match.
• The WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses.
• The OUTPUT clause returns a row for each row in the target that is inserted, updated, or deleted.
  
  

  Introduction

  One of the fantastic new features of SQL Server 2008 is Merge Statement. Using a single statement, we can Add/Update records in our database table, without explicitly checking for the existence of records to perform operations like Insert or Update.
  

  Facts about Merge Statement

  Here are a few facts that you must know before starting to use Merge Statement:
  
• Atomic statement combining INSERT, UPDATE and DELETE operations based on conditional logic
• Done as a set-based operation; more efficient than multiple separate operations
• MERGE is defined by ANSI SQL; you will find it in other database platforms as well
• Useful in both OLTP and Data Warehouse environments
  OLTP: merging recent information from external source
  DW: incremental updates of fact, slowly changing dimensions.

A typical merge statement looks like: 

MERGE [INTO] <target table>
USING <source table or table expression>
ON <join/merge predicate> (semantics similar to outer join)
WHEN MATCHED <statement to run when match found in target>
WHEN [TARGET] NOT MATCHED <statement to run when no match found in target>

Example

 

-- Update existing, add missing
MERGE INTO dbo.tbl_Customers AS C
USING dbo.tbl_CustomersTemp AS CT
        ON C.CustID = CT.CustID
WHEN MATCHED THEN
    UPDATE SET
      C.CompanyName = CT.CompanyName,
      C.Phone = CT.Phone
WHEN NOT MATCHED THEN 
      INSERT (CustID, CompanyName, Phone)

VALUES (CT.CustID, CT.CompanyName, CT.Phone)

CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);
CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);

--Synchronize source data with target
MERGE INTO dbo.tbl_Target AS t
    USING dbo.tbl_Source AS s    
        ON t.id = s.id
    WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN
        --Row exists and data is different
        UPDATE SET t.name = s.name, t.qty = s.qty
    WHEN NOT MATCHED THEN 
        --Row exists in source but not in target
        INSERT VALUES (s.id, s.name, s.qty) 
    WHEN SOURCE NOT MATCHED THEN 
        --Row exists in target but not in source
        DELETE OUTPUT$action, inserted.id, deleted.id