SQL MERGE STATEMENT is the combination of INSERT, UPDATE, and DELETE statements. Merge Statement can perform all these operations in our main target table when the source table is provided. MERGE is very useful when it comes to loading the data warehouse tables, which can be very large and require the specific actions to be taken when the rows are or are not present.
SQL Merge Statement
See the following syntax.
MERGE <target_table> [AS TARGET] USING <table_source> [AS SOURCE] ON <search_condition> [WHEN MATCHED THEN <merge_matched> ] [WHEN NOT MATCHED [BY TARGET] THEN <merge_not_matched> ] [WHEN NOT MATCHED BY SOURCE THEN <merge_matched> ];
#How to use SQL MERGE STATEMENT
- Identify the target table which is going to be used in that logic.
- The next step is to identify the source table which we can use in the logic.
- The next step is to determine the appropriate search conditions in the ON clause to match the rows.
- Implement logic when records are matched or not matched between the target and source.
- For each of these comparisons, conditions write the logic, and When matched, generally an update condition is used and When not matched, then insert or delete statement is used.
Let’s Clear this by seeing an example:
Consider Table Products: (This will be considered as Target Table).
Updated_Products: (This will be Considered as SOURCE Table).
MERGE PRODUCTS AS TARGET USING UPDATED_PRODUCTS AS SOURCE ON (TARGET.ID=SOURCE.ID) THEN MATCHED AND TARGET.NAME SOUCE.NAME OR TARGET.PRICE SOURCE.PRICE THEN UPDATE SET TARGET.NAME=SOURCE.NAME, TARGET.PRICE=SOURCE.PRICE WHEN NOT MATCHED BY TARGET THEN INSERT (ID, NAME, PRICE) VALUES (SOURCE.ID, SOURCE.NAME, SOURCE.PRICE) WHEN NOT MATCHED BY SOURCE THEN DELETE;
So, after running the above query Products table will be replaced by the Updated_products table.
You can see the table below.
So, in this way, we can perform all three operations together using the MERGE clause.
We can use any name other than source and target we have used these names to give you a better explanation.
#Some basic Key Points
- The MERGE SQL statement requires the semicolon (;) as a statement terminator Otherwise Error 10713 will be raised.
- At least one of three MATCHED clauses must be specified when we are using the MERGE statement.
- The user using the MERGE statement should have SELECT permission on the SOURCE table and INSERT, UPDATE and DELETE permissions on a TARGET table.
- While inserting, deleting or updating using merge statement in SQL Server fires any corresponding AFTER triggers defined on that target table, but it does not guarantee which action to fire triggers first or last.
Finally, SQL Merge Example is over.