SQL merge operators are table target operators that emulate
a DB2® MERGE command. A merge is a combination of
the SQL INSERT and UPDATE operations, otherwise known as an "upsert."
The SQL merge operator loads a DB2 table by checking the target table for a row that matches each row from the input data set. Two conditions define the operation of an SQL merge operator. The first condition, called the input match, matches rows from the input with existing rows in the target table. If no matching row is found, the row is inserted as a new row. If an input match is found, the second condition, called the target table match, checks a value in each input row against the associated row in the target table. If the condition matches, the row in the target table is updated. Separate column mapping properties allow you to choose one set of columns for any rows that meet the insert criteria and a different set of columns for the updates to act upon.
The following SQL MERGE statement is an example of the code that
is generated from a simple data flow that ends with an SQL merge operator.
In this case, the source table is stage_sls_sales_fact and the merge
target table is new_sls_sales_fact. The input match and target table
match conditions are highlighted in bold.
CREATE VIEW INPUT_02_0
(ORDER_DAY_KEY,
ORGANIZATION_KEY,
EMPLOYEE_KEY,
RETAILER_KEY,
RETAILER_SITE_KEY,
PRODUCT_KEY,
PROMOTION_KEY,
ORDER_METHOD_KEY,
SALES_ORDER_KEY,
SHIP_DAY_KEY,
CLOSE_DAY_KEY,
QUANTITY,
UNIT_COST,
UNIT_PRICE,
UNIT_SALE_PRICE,
GROSS_MARGIN,
SALE_TOTAL,
GROSS_PROFIT)
AS
SELECT ORDER_DAY_KEY AS ORDER_DAY_KEY,
ORGANIZATION_KEY AS ORGANIZATION_KEY,
EMPLOYEE_KEY AS EMPLOYEE_KEY,
RETAILER_KEY AS RETAILER_KEY,
RETAILER_SITE_KEY AS RETAILER_SITE_KEY,
PRODUCT_KEY AS PRODUCT_KEY,
PROMOTION_KEY AS PROMOTION_KEY,
ORDER_METHOD_KEY AS ORDER_METHOD_KEY,
SALES_ORDER_KEY AS SALES_ORDER_KEY,
SHIP_DAY_KEY AS SHIP_DAY_KEY,
CLOSE_DAY_KEY AS CLOSE_DAY_KEY,
QUANTITY AS QUANTITY,
UNIT_COST AS UNIT_COST,
UNIT_PRICE AS UNIT_PRICE,
UNIT_SALE_PRICE AS UNIT_SALE_PRICE,
GROSS_MARGIN AS GROSS_MARGIN,
SALE_TOTAL AS SALE_TOTAL,
GROSS_PROFIT AS GROSS_PROFIT
FROM DB2ADMIN."stage_sls_sales_fact" Q414 @
MERGE INTO "DB2ADMIN"."new_sls_sales_fact"
USING "INPUT_02_0"
ON ("DB2ADMIN"."new_sls_sales_fact"."RETAILER_KEY" = "INPUT_02_0"."RETAILER_KEY")
WHEN MATCHED AND ("DB2ADMIN"."new_sls_sales_fact"."SALE_TOTAL" > 10000) THEN
UPDATE SET
("ORDER_DAY_KEY", "ORGANIZATION_KEY", "EMPLOYEE_KEY", "RETAILER_KEY", "RETAILER_SITE_KEY",
"PRODUCT_KEY", "PROMOTION_KEY", "ORDER_METHOD_KEY", "SALES_ORDER_KEY", "SHIP_DAY_KEY",
"CLOSE_DAY_KEY", "QUANTITY", "UNIT_COST", "UNIT_PRICE", "UNIT_SALE_PRICE", "GROSS_MARGIN",
"SALE_TOTAL", "GROSS_PROFIT") = ( "INPUT_02_0"."ORDER_DAY_KEY", "INPUT_02_0"."ORGANIZATION_KEY",
"INPUT_02_0"."EMPLOYEE_KEY", "INPUT_02_0"."RETAILER_KEY", "INPUT_02_0"."RETAILER_SITE_KEY",
"INPUT_02_0"."PRODUCT_KEY", "INPUT_02_0"."PROMOTION_KEY", "INPUT_02_0"."ORDER_METHOD_KEY",
"INPUT_02_0"."SALES_ORDER_KEY", "INPUT_02_0"."SHIP_DAY_KEY", "INPUT_02_0"."CLOSE_DAY_KEY",
"INPUT_02_0"."QUANTITY", "INPUT_02_0"."UNIT_COST", "INPUT_02_0"."UNIT_PRICE",
"INPUT_02_0"."UNIT_SALE_PRICE", "INPUT_02_0"."GROSS_MARGIN", "INPUT_02_0"."SALE_TOTAL",
"INPUT_02_0"."GROSS_PROFIT")
WHEN NOT MATCHED THEN
INSERT
("ORDER_DAY_KEY", "ORGANIZATION_KEY", "EMPLOYEE_KEY", "RETAILER_KEY", "RETAILER_SITE_KEY",
"PRODUCT_KEY", "PROMOTION_KEY", "ORDER_METHOD_KEY", "SALES_ORDER_KEY", "SHIP_DAY_KEY",
"CLOSE_DAY_KEY", "QUANTITY", "UNIT_COST", "UNIT_PRICE", "UNIT_SALE_PRICE", "GROSS_MARGIN",
"SALE_TOTAL", "GROSS_PROFIT")
VALUES ( "INPUT_02_0"."ORDER_DAY_KEY", "INPUT_02_0"."ORGANIZATION_KEY", "INPUT_02_0"."EMPLOYEE_KEY",
"INPUT_02_0"."RETAILER_KEY", "INPUT_02_0"."RETAILER_SITE_KEY", "INPUT_02_0"."PRODUCT_KEY",
"INPUT_02_0"."PROMOTION_KEY", "INPUT_02_0"."ORDER_METHOD_KEY", "INPUT_02_0"."SALES_ORDER_KEY",
"INPUT_02_0"."SHIP_DAY_KEY", "INPUT_02_0"."CLOSE_DAY_KEY", "INPUT_02_0"."QUANTITY",
"INPUT_02_0"."UNIT_COST", "INPUT_02_0"."UNIT_PRICE", "INPUT_02_0"."UNIT_SALE_PRICE",
"INPUT_02_0"."GROSS_MARGIN", "INPUT_02_0"."SALE_TOTAL", "INPUT_02_0"."GROSS_PROFIT") @
DROP VIEW INPUT_02_0