Update strategy for table with sequence generated number as primary key in Informatica

Question

I have a mapping that gets data from multiple sql server source tables and assigns a sequence generated number as ID for each rows. In the target table, the ID field is set as primary key.

Every time I run this mapping, it creates new rows and assigns a new ID for the records that are pre-existing in the target. Below is an example:

1st run:
ID   SourceID   Name   State
1     123        ABC   NY
2      456       DEF   PA

2nd run:
ID   SourceID   Name   State
1     123        ABC   NY
2      456       DEF   PA
3     123        ABC   NY
4      456       DEF   PA

Desired Output must: 1) create a new row and assign a new ID if a record gets updated in the source. 2) create a new row and assign a new ID if new rows are inserted in the source.

How can this be obtained in Informatica?

Thank you in advance!

Could you flesh the question out more i.e. you already have a mapping which fulfills the requirements or there is some aspect of the requirement the mapping isnt fulfilling. Please advise which pinpointing specifically which part of the requirement isnt working — Daniel Machet, Sep 26 '17 at 19:28

score 0 · Answer 1 · answered Sep 26 '17 at 18:04

Give the ID field an IDENTITY PROPERTY...

Create Table SomeTable (ID int identity(1,1), 
                        SourceID int, 
                        [Name] varchar(64), 
                        [State] varchar(64))

When you insert into it... you don't insert anything for ID. For example...

insert into SomeTable
select
   SourceID,
   [Name],
   [State]
from
   someOtherTable

The ID field will be an auto increment starting at 1 and increment by 1 each time a row is inserted. In regards to your question about adding rows each time one is updated or inserted into another table, this is what TRIGGERS are for.

I think you meant to comment on the original post, not my answer @DanielMachet — S3S, Sep 26 '17 at 19:27

score 0 · Accepted Answer · answered Sep 26 '17 at 19:41

I'll take a flyer and assume the ACTUAL question here is 'How can I tell if the incoming record is neither insert nor update so that I can ignore it'. You could

a) have some date field in your source data to identify when the record was updated and then restrict your source qualifier to only pick up records which were last updated after the last time this mapping ran... drawback is if fields you're not interested in were updated then you'll process a lot of redundant records

b) better suggestion!! Configure a dynamic lookup which should store the latest state of a record matching by the SourceID. Then you can use the newlookuprow indicator port to tell if the record is an insert, update or no change and filter out the no change records in a subsequent transformation

Thank you for your suggestion Daniel! I used a dynamic lookup , router, and update strategy to achieve what I needed and its working perfectly :) — Subas, Sep 27 '17 at 01:15
Glad to hear. Thanks for taking the time to post back with your results — Daniel Machet, Sep 27 '17 at 05:44

Update strategy for table with sequence generated number as primary key in Informatica

2 Answers2