Tuesday, 7 April 2015

Target update override - Informatica

Target update override - Informatica

When you used an update strategy transformation in the mapping or specified the "Treat Source Rows As" option as update, informatica integration service updates the row in the target table whenever there is match of primary key in the target table found.

The update strategy works only 

  • when there is primary key defined in the target definition.
  • When you want update the target table based on the primary key.

What if you want to update the target table by a matching column other than the primary key? In this case the update strategy wont work. Informatica provides feature, "Target Update Override", to update even on the columns that are not primary key.

You can find the Target Update Override option in the target definition properties tab. The syntax of update statement to be specified in Target Update Override is

UDATE TARGET_TABLE_NAME  SET TARGET_COLUMN1 = :TU.TARGET_PORT1,      [Additional update columns]  WHERE TARGET_COLUMN = :TU.TARGET_PORT  AND   [Additional conditions]  

Here TU means target update and used to specify the target ports.

Example: Consider the employees table as an example. In the employees table, the primary key is employee_id. Let say we want to update the salary of the employees whose employee name is MARK. In this case we have to use the target update override. The update statement to be specified is


No comments:

Post a Comment