How do SQL MERGE statements work?

The SQL MERGE statement combines insert, update, and delete operations into a single query to synchronize a target table with data from a source table. It works by comparing rows between the target and source using a specified join condition. Depending on whether rows match, the MERGE executes different actions: updating or deleting existing rows in the target when matches are found, or inserting new rows when no matches exist. This eliminates the need for separate INSERT, UPDATE, or DELETE statements, streamlining data synchronization tasks like loading batches of new or modified records into a production table.

A typical MERGE statement includes a target table (the table being modified), a source (a table, view, or subquery providing input data), and a join condition that links the two. For example, suppose you have a Customers table and a staging table CustomerUpdates. To merge changes, you might write:

MERGE INTO Customers AS target 
USING CustomerUpdates AS source 
ON target.CustomerID = source.CustomerID 
WHEN MATCHED THEN 
 UPDATE SET target.Email = source.Email, target.Name = source.Name 
WHEN NOT MATCHED THEN 
 INSERT (CustomerID, Email, Name) 
 VALUES (source.CustomerID, source.Email, source.Name); 

Here, the statement checks if a CustomerID exists in both tables. If matched, it updates the email and name in Customers; if not, it inserts a new row. Optional clauses like WHEN NOT MATCHED BY SOURCE (for rows in the target not found in the source) can also trigger deletions or other logic.

Key considerations when using MERGE include transactional atomicity (all operations succeed or fail together) and performance. Proper indexing on the join columns (e.g., CustomerID) is critical to avoid full table scans. Be mindful of database-specific syntax differences: SQL Server and Oracle support MERGE, while PostgreSQL uses INSERT ... ON CONFLICT, and MySQL employs REPLACE or ON DUPLICATE KEY UPDATE. Test edge cases, such as multiple matching source rows (which can cause errors) or race conditions in concurrent environments. MERGE is ideal for batch operations but may require locking strategies for large datasets to prevent blocking.