How to implement update and insert in informatica

Question

I have Phone table with columns :

Clien_ID,Phone_num,Phone_Extention,Phone_Type,Last_Updated_Dt,Created_Dt

I am getting fixed width flat file which I will loading in staging table in sql.

I have to update the records in Phone table in terms of phone_num,Phone_Extention,Last_Updated_Dt for each phone type (based on matching Client ID) if Last_Updated_Dt of flat file record is greater than existing Last_Updated_dt and insert a new record of phone type of client does not exist.

How can I implement in informatica.

Lars G Olsen · Accepted Answer · 2017-09-17 17:11:44Z

1

SRC->SQ->LKP->RTR->TGT_ins
                 \>UPD_upd->TGT_upd

The SRC is the flatfile, the LKP is against your target (match on Client_ID), the UPD_upd should be set to DD_UPDATE, and now for the 'hard' part, the router and its ports:

all ports from the LKP should be prefixed with 'LKP_' when going into the router
the ports from the flatfile should be prefixed with 'SRC_'
Configure the router with two 'groups' and name the groups: Insert and Update
The condition on the Insert group is easy: isnull(SRC_Client_ID)
The condition on the Update group in your case is: SRC_last_Updated_dt>LKP_last_Updated_dt

answered Sep 17, 2017 at 17:11

Lars G Olsen

1,1178 silver badges12 bronze badges

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

3 Comments

Gooks Over a year ago

I will implement this week your solution and will let you know my results and in case of issues will comment.But thanks so much

Gooks Over a year ago

The update condition here would be used via Update Strategy transformation ?

Lars G Olsen Over a year ago

Yes - the UPD_upd is an update strategy transformation for the 'update' data stream. The insert stream don't need one, so I always omit it, but some find it better to include it... I ALWAYS use a router and different targets for ins/upd/delete, and never 'dd_reject' as a replacement for a filter/router since it gives much more useful log-data afterwards... all a matter of preference

Collectives™ on Stack Overflow

How to implement update and insert in informatica

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related