Update Strategy Transformation

Update strategy transformation is an active and connected transformation. Update strategy transformation is used to insert, update, and delete records in the target table. It can also reject the records without reaching the target table.

The design of the target table decides how to handle the changes to existing rows. In the informatica, you can set the update strategy at two different levels:

• Session Level: Configuring at session level instructs the integration service to either treat all rows in the same way (Insert or update or delete) or use instructions coded in the session mapping to flag for different database operations.
• Mapping Level: Use update strategy transformation to flag rows for inert, update, delete or reject.

Flagging Rows in Mapping with Update Strategy:

You have to flag each row for inserting, updating, deleting or rejecting. The constants and their numeric equivalents for each database operation are listed below.

• DD_INSERT: Numeric value is 0. Used for flagging the row as Insert.
• DD_UPDATE: Numeric value is 1. Used for flagging the row as Update.
• DD_DELETE: Numeric value is 2. Used for flagging the row as Delete.
• DD_REJECT: Numeric value is 3. Used for flagging the row as Reject.

The integration service treats any other numeric value as an insert.

IIF(department_id=10, DD_UPDATE, DD_INSERT)

Important Note:

Update strategy works only when we have a primary key on the target table. If there is no primary key available on the target table, then you have to specify a primary key in the target definition in the mapping for update strategy transformation to work.