Updating or Inserting records into table using informatica

Question

have two tables ACC and ACC_Restrict. I want to check the values of the column, P_R of either tables to see whether I should either insert, update or do nothing to ACC_Restrict. If the value is active in ACC but not in ACC_Restrict then insert into ACC_Restrict if it is still active in both ACC and ACC_Restrict then do nothing. If it is not active anymore in ACC but is still in ACC_Restrict then update ACC_Restrict by updating end_date to today's date. How can I achieve this in informatica?

Edit: There has been a modification of the requirement. ACC will only contain modifications to the account of someone. So if nothing has changed in someone's account we don't see anything in ACC but that doesn't mean if he had a restriction before, it has been lifted. It's just because nothing has changed so it doesn't appear in ACC. How do I manage this?

Note: P_R is a restriction on an account. I put the end_date to a date close to infinity and when the restriction is removed from the account I update ACC_Restrict by changing end_date to present date.

Answer 1

Hi ,

this we can use to implement type 1 scd,

UNCONNECTED_LKP SRC-->SQ-->EXP-->TGT

in session properties treat source row as update

in session mapping tab tick on update and update else insert

note:- the unconnected lookup is used to lookup on the target table and to derieve the max key and increment it for furthur new records key value using expression transformation..

if key value is already coming from source then no need for the looup

This article might be of interest for this:

http://www.disoln.org/2012/10/Update-With-Out-Update-Strategy-for-Better-Session-Performance.html