SQL Server: MERGE WHEN MATCHED/NO MATCHED Option

The MERGE statement allows DBA/Developer to combine the inserts, deletes, and updates, and to use a single statement to perform multiple DML actions. Using a MERGE statement instead of issuing multiple DML statements can improve performance. In a MERGE statement, DBA/Developer must specify a source and a target and include a join. Then, DBA/Developer use the MATCHED clauses to specify the actions to be performed. The basic syntax of the MERGE statement is as follows:

MERGE [INTO] target_table

USING source_table
ON join_condition
[WHEN MATCHED THEN matched_action]
[WHEN NOT MATCHED [BY TARGET]
THEN notmatched_action]
[WHEN NOT MATCHED BY SOURCE
THEN notmatchedsource_action];

The two WHEN NOT MATCHED THEN clauses specify the actions to take if the records from the source table are not in the target table, or vice versa. The WHEN MATCHED THEN clause specifies the action to take if the records from the source table are in the target table. When synchronizing tables, DBA/Developer can use BY TARGET or BY SOURCE to further control how the synchronization occurs when there are differences in the source data and the target data. In this scenario, DBA/Developer could use the following MERGE statement:

MERGE ARTrxMaster AS t

USING ARTrx AS s
ON (t.TrxID=s.TrxID) WHEN MATCHED THEN UPDATE SET
t.TrxType = s.TrxType, t.Quantity = s.Quantity, t.UnitPrice = s.UnitPrice, t.ExtAmt =
s.ExtAmt, t.TaxAmt = s.TaxAmt, t.LoadDate = GETDATE()
WHEN NOT MATCHED BY TARGET AND s.TrxType = 'TX1'
THEN INSERT(TrxID, TrxType, Quantity, UnitPrice, ExtAmt, TaxAmt, LoadDate)
VALUES (s.TrxID, s.TrxType, s.Quantity, s.UnitPrice, s.ExtAmt, s.TaxAmt,
GETDATE ())
WHEN NOT MATCHED BY SOURCE AND t.TrxType = 'TX1'
THEN DELETE
OUTPUT $action, INSERTED.*, DELETED.*;

With this statement, the following results would occur under these conditions:

The WHEN MATCHED clause would execute and update the target table (ARTrxMaster) if a row existed in both tables. The UPDATE statement does not include a table name because the table to be updated is implicit as the target table in the merge. The WHEN NOT MATCHED BY TARGET clause would insert rows into the target table (ARTrxMaster) if the row does not exist in the source table and the additional condition specified in the WHEN clause is met. Only rows in the ARTrx table that have a TrxType of 'TX1' would be inserted into ARTrxMaster. The WHEN NOT MATCHED BY SOURCE clause would delete rows from the target table (ARTrxMaster) which do not exist in the source table (ARTrx) if the additional condition specified in the WHEN clause is met. Only rows in ARTrxMaster with a TrxType of 'TX1' would be deleted. This statement also includes an OUTPUT clause. The OUTPUT clause allows DBA/Developer to retrieve and display information about the rows affected by the MERGE statement. The OUTPUT clause can display this information to the user, insert the data into another permanent or temporary table or table variable using an INTO clause, or pass the data to a nested DML statement for processing. Within the OUTPUT clause, DBA/Developer specify the column values that should be retrieved by using the column names with the INSERTED and DELETED prefixes. The DELETED prefix returns the column value before the DML operation, and the INSERTED prefix returns the column value after the DML operation but before executing any triggers. DBA/Developer can also use $action to return a string indicating which type of DML operation affected the row. In this statement, DBA/Developer specified $action, INSERTED.*, DELETED.*. This statement would return a result set of the rows affected by the MERGE, with the action that was performed for each row and the before and after values for each action. In this scenario, DBA/Developer could automate this synchronization process by using the MERGE statement in a job that is scheduled to run weekly.