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:

Informatica Mapping Insert Update Delete

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.

5 thoughts on “Informatica Mapping Insert Update Delete

  1. Praveen

    Very informative and helpful.

    I have one Quick question – i am not able to add Target and its corresponding SQ. When i add the Target, all of the ports are Output and i cannot change them to Input so that i can connect them to a new SQ.

    Also, when i try to add a new SQ for the Target, it pops up with a window to Select which Source it needs to be associated to.So, i am not sure on how to add a Target with SQ.

    Thank you once again.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.