CODEWITHSUNDEEP
data-warehouseinformaticainformatica-powercenterdata-management
vinay kumar
vinay kumar

Reputation: 31

DD_INSERT in the update strategy transformation

Informatica automatically inserts all the rows to the target...so why do we have to use update strategy transformation (DD_INSERT) to insert the records???

Upvotes: 1

Views: 2338

Answers (3)

Maciejg
Maciejg

Reputation: 3353

While it is true that insert is the default behavior, it may be changed on session properties. See the Treat Source Rows As property documentation.

Only when you want to build a logic and decide what to do with a particular row, you should use Data Driven property value and then use DD_INSERT and other values according to your needs.

Upvotes: 0

PraveenP
PraveenP

Reputation: 796

Update strategy transformation is used when you want handy logic to check insert/update/delete records by comparing source key columns with target key columns. for each type of update strategy transformation, you can choose how you want to process your data. below link examples by Informatica will help you to understand this:

https://docs.informatica.com/data-integration/powercenter/10-5/transformation-language-reference/constants/dd_insert/examples.html

You can achieve the same by using a lookup and setting IUD Flag in expression transformation and then based on each flag you can route different types of records (ins, upd, del) into different target to update target in different ways.

Informatica is giving you read to use logic in the form of Update strategy transformation and dd_insert is one type of that.

Upvotes: 0

Koushik Roy
Koushik Roy

Reputation: 7387

Good Question and you don't have to in case of insert only/update only case.

This assumption that informatica automatically inserts all the rows to the target is not true always. Yes, by default it inserts but there are many cases when we want to only update/only insert/delete/insert or update/reject the data.

Update strategy is used to control those scenarios.

DD_INSERT - This option is only to insert data.
DD_UPDATE - This updates the data based on key defined in target.
DD_REJECT - This rejects the data.
DD_DELETE - This deletes the data based on key defined in target.
Session should be data driven.

So, in your case, you can either mention dd_insert or set the session properties to insert only. if you want to insert new data but update old data, you need to use dd_insert or dd_update.
if you want to insert new data but ignore old data, you need to use dd_insert or dd_reject. if you want to only update old data, you need to use dd_update or set the session properties to update only..

Upvotes: 1

Related Questions