chandrarekha

 Profile Answers by chandrarekha 

• Nov 28th, 2007

 

It overrides the default update statemet in the target properties

seshasai.n

 Profile Answers by seshasai.n 

• Dec 25th, 2007

 

target update override it is also like souce qualifier override,target update override is use ful to update the target with out using the update strategy transformation.

miteshcpatel

 Profile Answers by miteshcpatel 

• Sep 12th, 2008

 

When we don't have primary keys defined on database level. And still we need update on this target from Informatica. We neeed to define keys at informatica level and use update override in target property. This way we can update the table.

ORAINFO

 Profile Answers by ORAINFO 

• Nov 18th, 2008

 

The Integration Service updates target tables based on key values. However, you can override the default UPDATE statement for each target in a mapping. You might want to update the target based on non-key columns.

When the Integration Service executes SQL against a source, target, or lookup database, it searches the reserved words file stored in the Integration Service installation directory. It encloses matching reserved words in quotes. If you use target update override, you must manually put all reserved words in quotes.

For a mapping without an Update Strategy transformation or a Custom transformation with the update strategy property enabled, configure the session to mark source rows as update. The Target Update option only affects source rows marked as update. The Integration Service processes all rows marked as insert, delete, or reject normally.

When you configure the session, mark source rows as data-driven. The Target Update Override only affects source rows marked as update by the Update Strategy or Custom transformation.

For example, a mapping passes the total sales for each salesperson to the T_SALES table.

The Designer generates the following default UPDATE statement for the target

T_SALES:UPDATE T_SALES SET EMP_NAME = :TU.EMP_NAME, DATE_SHIPPED = :TU.DATE_SHIPPED, TOTAL_SALES= :TU.TOTAL_SALES WHERE EMP_ID = :TU.EMP_ID

Because the target ports must match the target column names, the update statement includes the keyword:TU to specify the ports in the target transformation.

If you modify the UPDATE portion of the statement, be sure to use :TU to specify ports.

You can override the WHERE clause to include non-key columns.

For example, you might want to update records for employees named Mike Smith only. To do this, you edit the WHERE clause as follows:

UPDATE T_SALES SET DATE_SHIPPED = :TU.DATE_SHIPPED,TOTAL_SALES = :TU.TOTAL_SALES WHERE :TU.EMP_NAME = EMP_NAME andEMP_NAME = 'MIKE SMITH'