CODEWITHSUNDEEP
informaticainformatica-powercenter
karthik
karthik

Reputation: 261

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

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)

Upvotes: 1

Views: 1432

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

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

Upvotes: 1

Related Questions