Search For

UPSERT - Update else Insert

Flag Hide Line Nos
Flagging a Snippet, takes the snippet to Moderator Attention;

In order to handle the duplicate records, Informatica provides users to use UPSERT option to give priority to UPDATE operation first.

What it does ?
1. UPDATE will look for a PRIMARY KEY in Informatica target ports.
(You can change in Informatica target by making some columns as PRIMARY KEY.
Usually the primary index columns are well enough to make primary keys.)
2. It will update the existing record when a duplicate record arrives with the help of PRIMARY KEY defined in Target ports.
3. If a new record arrives the UPDATE will fail since PRIMARY KEY values in target port are not matching.
4. The new record will be inserted to the table at last. This logic will be continued for all the records.

What you have to do ?
1. In session level, go to properties, select "update" in "Treat all source rows as".
2. In session level, go to mapping tab, select target and set "Update else insert option" is checked.
3. In mapping level, go to Target and make some columns as PRIMARY KEYS (Usually primary index fields).

1. No records will go to any of load_work tables.
2. All the records will be handled by UPDATE else INSERT itself.
3. Very fast for small amount records.

Vote! You feel this code is working like charm ? Go a head Login and Press up arrow.
Didn't work ?, press down arrow and let us know the error in comments