SQL Server: Failover Capability and Read-Only Reporting Access

The MERGE statement allows DBA 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 must specify a source and a target and include a join. Then, DBA uses 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 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, you 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 you 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 specifies 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 can also use $action to return a string indicating which type of DML operation affected the row. In this statement, DBA specified action, INSERTED.*, DELETED.*. This statement would return a result set of the rows affected by the MERGE, action.