Text copied!
SQL Merge
MERGE is a powerful SQL statement used to perform insert, update, or delete operations on a target table based on the results of a join with a source table.
Merge allows you to keep two tables in sync easily or perform upserts (update existing rows or insert new rows if they do not exist).
Here's a general syntax for MERGE :
MERGE INTO target_table AS target
USING source_table AS source
ON <join_condition>
WHEN MATCHED THEN
UPDATE SET <update_clause>
WHEN NOT MATCHED THEN
INSERT (<column_list>) VALUES (<value_list>)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
• target_table : The table you want to change.
• source_table : The table providing the data for the changes.
• <join_condition> : How to match rows between the two tables.
• <update_clause> : Which columns to update and what values to use, for matched rows.
• <column_list> and <value_list> : Columns and their values to add to the target table if no match is found.
• WHEN NOT MATCHED BY SOURCE THEN DELETE: This part is optional and It deletes rows from the target table that don't have matches in the source table.
Here's a simplified example of using the MERGE statement in SQL :
1. Let's assume you have a database with two tables : [i]. Product [ii]. Product2
2. The [Product] table houses 3 records, while the [Product2] table currently accommodates only 1 record sourced from the Product table, where the product_name is NULL.
3. Now, you want to update the [Product2] table with the data from [Product], performing inserts for new product and updates for existing ones. Run below SQL statement :
MERGE INTO [Product2] AS target
USING [Product] AS source
ON (target.Product_Id = source.Product_Id)
WHEN MATCHED THEN
UPDATE SET target.Product_Id= source.Product_Id,
target.[Name]= source.[Name],
target.GroupName = source.GroupName,
target.ModifiedDate = source.ModifiedDate
WHEN NOT MATCHED THEN
INSERT (Product_Id, [Name], GroupName, ModifiedDate)
VALUES (source.Product_Id,source.[Name], source.GroupName, source.ModifiedDate);
4. This MERGE statement will update existing records in [Product2] with matching Department_Id from [Product] and insert new records from Product into [Product2].
Great job! You've successfully merged the tables!