Update Strategy transformation
Purpose
Specifies how each row updates target tables (insert, update, delete, or reject) based on an expression.
The Update Strategy transformation "tags" a row with the appropriate DML (data manipulation language) for the PowerCenter writer to apply to a relational target. Each row can be "tagged" with one of the following flags (the DD label stands for Data Driven).
Appropriate SQL is submitted to the target database:
DD_INSERT for insert
DD_DELETE for delete
DD_UPDATE for update
DD_REJECT means the row will not have SQL written for it
Examples / useful tips
IIF(row_exists_flag = 1, DD_UPDATE, DD_INSERT)
For the row tags DD_DELETE and DD_UPDATE, the table definition in a mapping must have a key identified otherwise the session created from that mapping will fail.
Rows tagged with DD_REJECT will be passed on to the next transformation or target and subsequently placed in the appropriate "bad file" if the "Forward Rejected Rows" attribute is "checked" (default). If the attribute is "un-checked" then reject rows will be skipped.
In some cases there may be a performance benefit to split a mapping with updates and inserts into two mappings and sessions: one with inserts and the other with updates.