How to Implement Insert , Update and Delete In the Same Mapping in Informatica

Question

We have a requirement where we have to Implement Insert, Update and delete Logic under the Same Mapping In Informatica. The Source and target table structures are the same and there is no difference. There is no primary key in the target and we are Defining PERSON_KEY as the primary key in the Informatica mapping to update records, I am currently using a look upon the target table and comparing the records and in the router transformation, The Following logic is used for insert and Updates. I have also provided the Table structure for reference.

NOTE: The Source and target are from Different Database

INSERT : ISNULL(LKP_PERSON_KEY)
UPDATE : IIF(PERSON_KEY=LKP_PERSON_KEY,TRUE,FALSE)

PERSON_KEY      NUMBER(7, 0) ,
SOURCE_CD       VARCHAR2(16 CHAR) ,
SOURCE_INSTANCE NUMBER(2, 0) ,
TYPE_CD         VARCHAR2(32 BYTE) ,
TYPE_INSTANCE   NUMBER(2, 0) ,
VALUE           VARCHAR2(255 CHAR) ,
VALUE_TEXT      VARCHAR2(255 CHAR) ,
UPDATE_SOURCE   VARCHAR2(32 CHAR) ,
UPDATE_ACCOUNT  VARCHAR2(32 CHAR) ,
UPDATE_DATETIME DATE ,
UPDATE_SUNETID  VARCHAR2(64 CHAR) ,
UPDATE_COMMENT  VARCHAR2(255 CHAR)

Koushik Roy · Accepted Answer · 2022-03-24 09:11:06Z

1

use update strategy.

Make sure PERSON_KEY is defined as PK in target designer.
Then in the mapping, right before target, add expression transformation. Pull in all data columns and PERSON_KEY,LKP_PERSON_KEY columns. Create a column with below logic. I assumed, if none of above condition met, delete the data.

out_insert_update_flag = 
IIF(ISNULL(LKP_PERSON_KEY), 'INSERT',IIF(PERSON_KEY=LKP_PERSON_KEY,'UPDATE','DELETE')
)

Then add the update strategy between expression transformation and target. Pull in all required columns and out_insert_update_flag. And create a logic like this-

IIF(out_insert_update_flag ='INSERT', DD_INSERT, IIF(out_insert_update_flag='UPDATE', DD_UPDATE, DD_DELETE
))

Then add all required columns to target.

In session - pls set load strategy to data driven.

Mapping should look like -

.... --> EXP-->UPD-->TGT

answered Mar 24, 2022 at 9:11

Koushik Roy

7,4392 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maciejg Over a year ago

I usually use a router to split INSERT/UPDATE/DELETE pipelines and use three target instances named accordingly. While technically there is no difference to this solution discussed by Koushik, it has following advantages: UPDATE strategy condition is plain and simple (each one uses just DD_INSERT/DD_UPDATE/DD_DELETE), it's clearly visible in the mapping what is going on and (last but not least) in the Monitor stats you can clearly see number of performed inserts/updates/deletes.

Koushik Roy Over a year ago

thats also a good idea @Maciejg...

Collectives™ on Stack Overflow

How to Implement Insert , Update and Delete In the Same Mapping in Informatica

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related