Informatica Mapping Insert Update Delete

There are situations where you need to keep a source and target in sync.  One method to do this is to truncate and reload.  However this method is not that efficient for a table with millions of rows of data.  You really only want to:

• insert rows from the source that don’t exist in the target
• update rows that have changed
• delete rows from the target that no longer exist in the source

Can you do this efficiently in a single informatica mapping?

Here is a picture of the informatica mapping:

Here is the detailed informatica mapping:

1. Insert Source and Source Qualifier from Source
2. Insert Source and Source Qualifier from Target
3. Sort Source and Target in Source Qualifiers by Key Fields
4. Insert Joiner Transformation using a Full Outer Join and select Sorted Input option
5. Insert a Router Transformation with 3 groups
   1. Insert – ISNULL(Target_PK)
   2. Delete – ISNULL(Source_PK)
   3. Default – used for Update
6. Insert an Update Transformation coming form the Delete Group using DD_DELETE
   1. Connect this transformation to the Target
7. Insert a Filter Transformation coming from the Update Group
   1. (
      DECODE(Source_Field1, Target_Field1, 1, 0) = 0
      OR
      DECODE(Source_Field2, Target_Field2, 1, 0) = 0
      )
   2. Modify as needed to compare all non-key fields
8. Insert an Update Transformation coming from the Filter Transformation using DD_UPDATE
   1. Connect this transformation to the Target
9. Connect the Insert Group in the Router Transformation to the Target

Please leave a comment if you have questions on this informatica mapping process.