Lab
Lab #11: MERGE Statement
Learn how to write MERGE statements with outer-join queries. Typical outer join queries in a MERGE statement return a surrogate key for a target table when the rows intersect with the transaction table; and the MERGE statement updates these rows. The outer join queries return a null value when they don’t intersect with the target table; and the MERGE statement inserts these rows as new rows. Learn how to query aggregated result sets from the transaction table.
Objectives
You learn how to use the MERGE statement to insert or update rows in target tables; and how to bundle three MERGE statements into a single upload_transaction procedure.
All SQL Data Manipulation Language (DML) commands are ACID Compliant by themselves. Transactions that combine two or more DML statements together require you to use Transaction Control Language (TCL) to manage their combined ACID-compliance.
Business Scenario
Business analysts typically work with information rather than data. The difference between information and data from a database perspective is interesting and quite simple. Information is constructed by joining single subject facts from tables into the results from SELECT statements.
As a rule when business analysts have fixed or modified information, the information technology teams must put the modified data back into the correct tables. The process of deconstructing information into data is done step-by-step, which means you update one table at a time. Each table MERGE statement of new or modified data must succeed before you MERGE data into dependent tables.
The process also requires that all MERGE statement succeed or fail. That means you need to use TCL to ensure that any MERGE statement that completes before a failure can be unwritten. A stored procedure can ensure the all or none guarantee works across a series of MERGE statements or any other DML statements.
