Definition: The MERGE statement in SQL is a powerful command that combines data from two tables into a single table. It is used to perform a variety of data manipulation operations, such as updating, deleting, or inserting data into a target table based on the data available in a source table.
The MERGE statement compares the data in the source table with the data in the target table based on a specified join condition. The join condition is typically a set of columns that are common between the two tables, such as primary keys, and specifies how the two tables should be matched.
Once the tables are matched, the MERGE statement performs the appropriate operation, depending on the specified action. For example, if the source table contains a record that matches a record in the target table, the MERGE statement can update the existing record or delete it, depending on the specified action. If the source table contains a record that does not match any records in the target table, the MERGE statement can insert a new record into the target table.
Syntax: Step by step explanation
- Specify the target table using the MERGE keyword followed by the target table name.
MERGE target_table
- Specify the source table using the USING keyword followed by the source table name.
MERGE target_table
USING source_table
- Specify the ON condition that specifies how the data from the source table should be matched with the data in the target table. This is done using the ON keyword followed by the condition that specifies how the tables should be joined.
MERGE target_table
USING source_table
ON target_table.column_name = source_table.column_name
- Specify the different actions that can be taken based on the results of the join by using the WHEN MATCHED and WHEN NOT MATCHED keywords.
WHEN MATCHED is used when the condition specified in the ON clause is satisfied and a matching row is found in both tables.
MERGE
Target_Table
USING
Source_Table
ON
Target_Table.Column_Name = Source_Table.Column_Name
WHEN MATCHED
THEN
UPDATE
SET
Target_Table.Column1 = Source_Table.Column1,
Target_Table.Column2 = Source_Table.column2;
(In this example, the UPDATE action is performed on the matched row, updating column1 and column2 in the target table with the values from the source table. Other actions such as DELETE or OUTPUT can also be specified in the WHEN MATCHED clause.
- WHEN NOT MATCHED is used when the condition specified in the ON clause is not satisfied and no matching row is found in the target table
MERGE
Target_Table
USING
Source_Table
ON
Target_Table.Column_Name = Source_Table.Column_Name
WHEN
NOT MATCHED
THEN
INSERT
(column1, column2)
VALUES (
source_table.column1,
Source_table.column2
);
(In this example, the INSERT action is performed on the non-matching row, inserting column1 and column2 from the source table into the target table. Other actions such as UPDATE, DELETE or OUTPUT can also be specified in the WHEN NOT MATCHED clause.)
Example:1 Updating existing rows
MERGE
dbo.Customers AS target
USING
dbo.CustomerUpdates AS source
ON (
target.CustomerID = source.CustomerID
)
WHEN
MATCHED
THEN
UPDATE
SET
Target.FirstName = Source.FirstName,
Target.LastName = Source.LastName,
Target.Email = source.Email;
(This will update the FirstName, LastName, and Email columns in the Customers table with the values from the CustomerUpdates table when a match is found based on the CustomerID column.)
Example:2 Inserting new rows
MERGE
dbo.Sales AS target
USING
dbo.SalesUpdates AS source
ON (
Target.OrderID = source.OrderID
)
WHEN
NOT MATCHED BY target
THEN
INSERT
(OrderID, ProductID, Quantity)
VALUES
(
Source.OrderID,
Source.ProductID,
Source.Quantity
);
(This will insert new rows into the Sales table with the OrderID, ProductID, and Quantity values from the SalesUpdates table when no match is found based on the OrderID column.)
Example:3 Deleting rows
MERGE
dbo.Employees AS target
USING
dbo.EmployeeUpdates AS source
ON (
Target.EmployeeID = Source.EmployeeID
)
WHEN
MATCHED AND source.IsActive = 0
THEN
DELETE;
(This will delete the rows from the Employees table where the IsActive column in the EmployeeUpdates table is 0 and a match is found based on the EmployeeID column.)
Conclusion: MERGE statement is a useful tool for performing complex data manipulation tasks in SQL. It allows you to perform multiple operations in a single statement, reducing the complexity of your code and making it easier to manage and maintain. However, it is important to use the MERGE statement carefully and test it thoroughly to ensure that it performs as expected and does not cause any unintended consequences.