SQL MERGE statements provide a powerful way to perform insert, update, or delete operations on a target table based on the results from a source table. This single statement helps to synchronize the two tables by merging data efficiently. For instance, you might have a target table with customer information and a source table with updated details. The MERGE statement can be used to update existing records if there is a match, insert new records if there’s no match in the target, or even delete records based on specific conditions.
The syntax of a MERGE statement typically starts with the MERGE INTO keyword followed by the target table. Then, you specify the USING clause to define the source table. A matching condition is established using the ON clause. Inside the statement, you outline what action to take in the event of a match with the WHEN MATCHED clause, which often consists of an UPDATE command. Conversely, you can use the WHEN NOT MATCHED clause to handle cases where no match is found, usually resulting in an INSERT operation.
Here's a basic example to illustrate. Suppose you have a target table called Products and a source table named NewProducts. You want to update the price of products that exist in both tables and insert any new products from NewProducts that do not yet exist in Products. The MERGE statement could look like this:
MERGE INTO Products AS target
USING NewProducts AS source
ON target.ProductID = source.ProductID
WHEN MATCHED THEN
UPDATE SET target.Price = source.Price
WHEN NOT MATCHED THEN
INSERT (ProductID, ProductName, Price)
VALUES (source.ProductID, source.ProductName, source.Price);
In this case, if the ProductID from NewProducts exists in Products, the price will be updated; if it does not exist, a new product will be inserted with the provided details. This makes the MERGE statement not only versatile but also a cleaner way to handle complex data updates, ensuring your database remains consistent and up-to-date with minimal code.