For an instructor lead, in-depth look at learning SQL click below.
SQL (Structured Query Language) is a powerful tool that programmers use to communicate with databases. Among the numerous statements and functions available in SQL, the MERGE statement holds a unique position. Often dubbed as the “Swiss Army Knife of SQL statements,” the MERGE statement is your go-to SQL command when you want to perform ‘INSERT’, ‘UPDATE’, and ‘DELETE’ at the same time. It’s a convenient method to synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in another table.
Basic Syntax of the MERGE statement
The basic syntax of the MERGE statement is as follows:
|
1 2 3 4 5 6 7 8 |
MERGE [Target Table] AS T USING [Source Table] AS S ON [Search condition] WHEN MATCHED THEN UPDATE SET T.[column_name] = S.[column_name] WHEN NOT MATCHED BY TARGET THEN INSERT ([column_name]) VALUES(S.[column_name]) WHEN NOT MATCHED BY SOURCE THEN DELETE; |
This syntax allows SQL to know which table to update (target table) and where to find the data (source table). A specified search condition determines a match between the source and target tables. When matched, it will update the target record. When the source has a record that isn’t in the target, it will insert the record into the target. If the target has a record that isn’t in the source, it will delete the record.
Example of MERGE Statement
Consider the two simple tables Customers (target) and NewCustomers (source) which share a common column ‘CustomerID.’
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--Customers Table CREATE TABLE Customers (CustomerID INT PRIMARY KEY, CustomerName NVARCHAR(100), Email NVARCHAR(100)) --NewCustomers Table CREATE TABLE NewCustomers (CustomerID INT PRIMARY KEY, CustomerName NVARCHAR(100), Email NVARCHAR(100)) |
To synchronize the Customers table with the NewCustomers table, the MERGE statement will be:
|
1 2 3 4 5 6 7 8 9 10 |
MERGE Customers AS T USING NewCustomers AS S ON (T.CustomerID = S.CustomerID) WHEN MATCHED THEN UPDATE SET T.CustomerName = S.CustomerName, T.Email = S.Email WHEN NOT MATCHED BY TARGET THEN INSERT (CustomerID, CustomerName, Email) VALUES(S.CustomerID, S.CustomerName, S.Email) WHEN NOT MATCHED BY SOURCE THEN DELETE; |
The above MERGE statement will update the existing records in Customers that match the CustomerID in NewCustomers. It will also insert the new records from NewCustomers into Customers, and any records in Customers that don’t have a match in NewCustomers will be deleted. This way, the two tables become synchronized.
Conclusion
The MERGE statement is an efficient way to perform multiple DML operations in a single statement. However, one needs to exercise caution while using it because improper use can lead to unexpected data loss. Always remember to validate the data and test your MERGE statement before executing it on a production database.