Update without update strategy in informatica : Last time we went through update strategy transformation in informatica with example. Update Strategy transformation let you insert, update , delete data on target side . It can also reject the incoming data depending upon your condition . But one question arise , can we update without update strategy in informatica ? Lets go through below article , how can we update records in informatica even without update strategy transformation and what is the difference between these two approaches ?
Check here for how to filter null values in informatica
Update Records by update strategy transformation
We saw one example of update strategy transformation in informatica , where we update/insert/delete records in employee table. While using update strategy transformation , we update the flag of records for insert/update/delete in update strategy expression in update strategy transformation
Below Flags can be set in Update strategy Expression.
|Flag||Numeric Value||Type Operation|
Update without update strategy in informatica
We can also update records in informatica by changing the properties of “Treat Source rows as” in Properties tab of session task.
During session configuration, you can select a single database operation for all rows using the Treat Source Rows as setting from the ‘Properties’ tab of the session.
- Insert :- Treat all rows as inserts.
- Delete :- Treat all rows as deletes.
- Update :- Treat all rows as updates.
- Data Driven :- Integration Service follows instructions coded into Update Strategy flag rows for insert, delete, update, or reject.
Please note that we need to update Treat Source Rows as Data Driven while using update strategy transformation. Otherwise it will ignore flag in update strategy expression and will treat all rows with same operation as mentioned in Treat Source Rows.
When we select Treat Source Rows as update , we can set more option in Target option.
Update: Under Update set below sub option
- Update as Update. Update each row flagged for update if it exists in the target table.
- Update as Insert. Inset each row flagged for update.
- Update else Insert. Update the row if it exists. Otherwise, insert it
- Truncate Table :- Select this option to truncate the target table before loading data
So in case we want to update all records , we can set below option.
Properties tab in session:-
Set Treat Source Row as Update
Target Table option :-
- Check Insert
- Uncheck Update as Update
- Uncheck Update as Insert
- Check Update else Insert
So in this article , we went through explanations how to update without update strategy in informatica.