Add a new snippet
UPSERT - Update else Insert in Informatica
UPSERT - Update else Insert
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.
Tags for UPSERT - Update else Insert in Informatica
UPSERT or Update else insert in Informatica
update else insert option in Informatica
how to do upsert in informatica
informatica update as insert
informatica update else insert not working
what is upsert in informatica
All columns marked as primary keys. UPDATEs will have no effect in informatica
Like Forget Code ?