SQL Merge Statement Tutorial With Example | Merge In SQL

SQL Merge Statement Tutorial With Example is today’s topic. SQL MERGE STATEMENT is the combination of INSERT, UPDATE, and DELETE statement. 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

The syntax is following.

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.
Next step is to identify the source table which we can use in the logic.
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 this comparison, 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).

ID	NAME	PRICE
101	Tea	5.00
201	Chips	10.00
301	Coffee	15.00

Updated_Products: (This will be Considered as SOURCE Table).

ID	NAME	PRICE
101	Tea	5.00
201	Biscuits	20.00
301	Coffee	25.00

#QUERY

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;

#Output

So, after running the above query Products table will be replaced by the Updated_products table.

You can see the table below.

ID	NAME	PRICE
101	Tea	5.00
201	Biscuits	20.00
301	Coffee	25.00

So, in this way, we can perform all three operations together using MERGE clause.

NOTE:

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 Tutorial With Example is over.

The post SQL Merge Statement Tutorial With Example | Merge In SQL appeared first on AppDividend.

via Planet MySQL
SQL Merge Statement Tutorial With Example | Merge In SQL