CODEWITHSUNDEEP
informatica-powercenterinformatica
Incognito
Incognito

Reputation: 3094

Handling Delete followed by Insert in Informatica Designer

I am working on Informatica PowerCenter Designer 8.1.1

I have a source table with three columns, which are

PORT_ID
ISSUE_ID
MKT_VAL

I need to sync the data from my source table to a target table which contains the same three tables on a different database.

There's a 1:n relationship between PORT_ID and ISSUE_ID

While doing this data Sync, I have to do a DELETE followed by INSERT, reason being, the number of ISSUE_ID mapped to a PORT_ID can change. Let's say that intially, the data was like this in Source and Target:

PORT_ID    ISSUE_ID
1          A
1          B
1          C

The data in source gets changed to:

PORT_ID    ISSUE_ID
1          A
1          B
1          D

Due to this, during my sync, I have to first delete all rows mapped to PORT_ID = 1 and then Insert the incoming records.

I am not able to figure out how I can get this done in mapping designer. Can someone give me some inputs?

Upvotes: 1

Views: 8475

Answers (4)

Aswin
Aswin

Reputation: 111

I dont think we need a dynamic lookup here, as the requirement is not having dups in the source..

Why dont you do a regular lookup and update the records using Update strategy instead of delete and insert?

Upvotes: 0

Shirish
Shirish

Reputation: 47

You can create dynamic lookup on the source table.

What you can do to create dynamic lookup,

-> go to the lookup properties,

-> check the dynamic lookup cache box

-> and then check the insert else update box.

As soon as you do that one new port NewLookupRow will appear in the ports tab. You can use this port to check whether record is insert or update with following corresponding values

0 is no change

1 is insert

2 is update

Now you can update the target accordingly.

Hope this helps..

Cheers.

Upvotes: 0

Sandeep
Sandeep

Reputation: 41

Write a simple stored proc which does the following:

1) Delete statement (Given by Rajesh) (delete from tgt_table where (port_id, issue_id) not in (select port_id, issue_id from src_table ); commit; )

2) Insert Statement 

Insert into tgt_table where not in src_table
 commit;

3) Use dummy as source and target in mapping, and call the stored proc using STORED_PROCEDURE_TRANSFORMATION.

Upvotes: 0

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

The most common way this is done is using a pre-query. If port_id and issue_id are unique within the table, you could use....

delete from tgt_table
where (port_id, issue_id)
  not in (select port_id, issue_id
            from src_table
         );
commit;

Second Way:

If these two columns can be added as a key in your mapping, then you can "check" the treat target rows as "insert, update, delete" (all three) to make sure the target data is the same as the source data. In most cases, however, business rules are more complex than this, so this feature is rarely used.

Another common implementation is to "Mark rows for delete" based on a lookup on the target table.

   Source -> Lookup (target_table) -> 
   exp (flag to see if the value exists) -> 
   mark for delete ->
   Delete using update_strategy_transformation

Upvotes: 1

Related Questions