top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Handling Delete followed by Insert in Informatica Designer

+2 votes
716 views

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?

posted Mar 19, 2014 by Rohini Agarwal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

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
answer Mar 19, 2014 by Shweta Singh
Similar Questions
0 votes

I am new to Informatica and I am trying to learn it bit by bit.Currently I am able to enable the repository services but nothing works after I get connected. As in the transformation icons do not get highlighted when I open the mapping designer. Please find the attached screen-shot. Let me know if any questions.
enter image description hereThank you.

0 votes

My source File Description:
In creating a mapping in Informatica designer where in my source is a fat file which is the collection of different types of data, as in customer details from customer table, his enrollment details from enrollment table and few more tables like this.
In this scenario how do I proceed in creating mapping and transformation to target?
If it were a single source file I define a simple source and target mapping.

0 votes

I'm using Informatica Power Center 9.1.0 and have blocked with below usecase.

For any given key, delete existing records from target table and insert the records from soruce to same target table and both delete and insert should happen in a single transaction.

Source data:

BusDate   Order Product Quantity
21-May    O1    Phone        100
21-May    O1    Tab           50
21-May    O1    Cam           75
21-May    O1    PC           200
21-May    O2    Phone        150 
21-May    O2    PC            50

Source data is as mentioned in above tabular fomrat, when we are writing to target, delete existing data for given business date and order id combination and insert new data in same transaction.

In above example for Order 1 (O1) and BusDate 5/21 we want to delete all existing records in target table and insert all 4 records in same transaction.

Here how it looks if I need to do same in SQL:

BEGIN TRANSACTION

DELETE target_table
WHERE BusDate = '5/21/2014'
AND Order = 'O1'

INSERT target_table
SELECT *
FROM input_table
WHERE BusDate = '5/21/2014'
AND Order = 'O1'

COMMIT TRANSACTION
...