Update strategy for table with sequence generated number as primary key in Informatica

Question

I have a mapping that gets data from multiple sql server source tables and assigns a sequence generated number as ID for each rows. In the target table, the ID field is set as primary key.

Every time I run this mapping, it creates new rows and assigns a new ID for the records that are pre-existing in the target. Below is an example:

1st run:
ID   SourceID   Name   State
1     123        ABC   NY
2      456       DEF   PA

2nd run:
ID   SourceID   Name   State
1     123        ABC   NY
2      456       DEF   PA
3     123        ABC   NY
4      456       DEF   PA

Desired Output must: 1) create a new row and assign a new ID if a record gets updated in the source. 2) create a new row and assign a new ID if new rows are inserted in the source.

How can this be obtained in Informatica?

Thank you in advance!

Answer 1

I'll take a flyer and assume the ACTUAL question here is 'How can I tell if the incoming record is neither insert nor update so that I can ignore it'. You could

a) have some date field in your source data to identify when the record was updated and then restrict your source qualifier to only pick up records which were last updated after the last time this mapping ran... drawback is if fields you're not interested in were updated then you'll process a lot of redundant records

b) better suggestion!! Configure a dynamic lookup which should store the latest state of a record matching by the SourceID. Then you can use the newlookuprow indicator port to tell if the record is an insert, update or no change and filter out the no change records in a subsequent transformation