Update Strategy Transformation

Update Strategy Transformation is Connected and Active transformation. It is used to insert, update, delete and reject records from a file or table while loading into the target. Update Strategy flags row type to insert or update or delete as per the requirement.

In order to perform insert, update, delete and reject records we have to flag the rows with certain key words like 

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.

We have to select the option in session properties 'treat source rows as' Data driven, but this alone will not let you modify rows in the target. Also check the option in Target level properties as 'Update else insert' to apply the Update strategy transformation to flag the rows.


There are three target level properties:-
--------------------------------------------
Update else insert : It property tells, when a row arrives target, if it is a new row it will be inserted, if its a old row it should be updated.
Update as update : It property tells, if row arrives at the target, it has to be update in the target.
Update as insert : It property tells, when a row arrives target and if that row has to be updated then that update behavior should be a insert. 

Also select insert option in target level properties for update as insert and update else insert. Otherwise, session fails thinking that target does not allow inserts, its because these clauses have insert in them.

We cannot load the data into the target without defining primary keys on the target table, when we use update strategy transformation. Session generates error as 'target has no keys specified'.

But we override the target definition by using the option in properties tab of target definition 'Update override'.
We can use option 'Generate SQL', to get update override syntax (or) we can write our own syntax, as per the requirement

Target Override Query is 
'UPDATE TGT_EMP SET NAME=TU.NAME,DNO=TU.DNO, SAL=TU.SAL WHERE EMPNO=TU.EMPNO'.