The update strategy determines whether to insert, delete, update, or reject records.
In PowerCenter, you set it at two different levels:
You can also use the Custom transformation to flag rows for insert, delete, update, or reject.
Constant | Integer litteral | Description |
---|---|---|
DD_DELETE | 2 | Delete the record |
DD_INSERT | 0 | Insert the record |
DD_REJECT | 3 | Used to filer or validate data. The record is skipped and written to the session reject file |
DD_UPDATE | 1 | Update the record |
The following statement marks items with an ID of 1001 for deletion and all other for insertion
IFF ( ITEM_ID = 1001, DD_DELETE, DD_INSERT )
You can set session-level target properties to specify how the Integration Service inserts, updates, and deletes rows.
Property | Description | Default |
---|---|---|
Update (as Update) | Integration Service updates all rows flagged for update. | Enabled |
Update (as Insert) | Integration Service inserts all rows flagged for update. | Disabled |
Update (else Insert) | Integration Service updates rows flagged for update if they exist in the target, then inserts any remaining rows marked for insert. | Disabled |
Delete | Integration Service deletes all rows flagged for delete. | Disabled |
Truncate Table | Integration Service truncates the target before running the session. | Disabled |