The MERGE statement, often referred to as an "upsert", is a powerful SQL command that allows you to insert, update, or delete records in one table based on the source data from another table. It's a combination of the INSERT, UPDATE, and DELETE operations.
MERGE INTO target_table AS target
USING source_table AS source
ON (target.column_name1 = source.column_name1)
WHEN MATCHED THEN
UPDATE SET target.column_name2 = source.column_name2,...
WHEN NOT MATCHED THEN
INSERT (column1, column2,...) VALUES (value1, value2,...);
Let's assume we have two tables - Employees (the target table) and NewData (the source table). We want to update the salary of employees in the Employees table with the data from the NewData table where the IDs match. If there's no match, we want to insert the new record.
MERGE INTO Employees AS e
USING NewData AS nd
ON (e.EmployeeID = nd.EmployeeID)
WHEN MATCHED THEN
UPDATE SET e.Salary = nd.Salary
WHEN NOT MATCHED THEN
INSERT (EmployeeID, Name, Salary) VALUES (nd.EmployeeID, nd.Name, nd.Salary);
This operation will ensure that all existing employee salaries are updated based on the NewData table, and any new employees found in the NewData table are added to the Employees table.
ON clause uses a column (or set of columns) that can uniquely identify records in both tables.MERGE statement is atomic, meaning it's an all-or-nothing operation. If any part fails, the entire operation is rolled back.DELETE operation in a MERGE statement. It can be powerful but potentially destructive if not used judiciously.