When you are pulling data from source and based on target's primary key if you are just updating it, without any complected logic, then you can update those records without using update strategy.
Lets have a quick glance at below table..
By Default Rows coming from Source Qualifier has type as Insert; we use update strategy to change that type from Insert to Delete / Update
1. Data Driven -- When we select treat source rows option as Data Driven, mapping will get the priority. And if we change the incoming row type from insert to update / delete using Update Strategy, corresponding rows will get updated / deleted.
2. Update -- When we select treat source rows option as Update, all rows coming from source will be treated for Updation by default and your session / task will get the priority. You don't need update strategy at Mapping level to change the type.
Example --
Let's create a simple mapping which will insert data into target table and at session level choose Treat Source Rows As option to "Update"
Considering your target as relational table, you need to select below options at target level in your session
As stated in first table, when you select any option mentioned below, you have to select Insert check box by default
1. Update As Update
2. Update As Insert
3. Update else Insert
Note -- if you do not select the Insert check box, and run the mapping like that, you will get below error as all records will get rejected.
Severity Timestamp Node Thread Message Code Message
ERROR 3/18/2014 7:15:47 PM node01_gogate WRITER_1_*_1 WRT_8118
ERROR: Target table [Countries2] does not allow UPDATE
Row # [0] in bad file
Below is the screen shot from infra help files.
So, if you run the mapping now - since you do not have any records at target - all records will be first get inserted.
If you run your mapping again, and since now you have all records present at target, now same record set will be updated.
Lets have a quick glance at below table..
1. Data Driven -- When we select treat source rows option as Data Driven, mapping will get the priority. And if we change the incoming row type from insert to update / delete using Update Strategy, corresponding rows will get updated / deleted.
2. Update -- When we select treat source rows option as Update, all rows coming from source will be treated for Updation by default and your session / task will get the priority. You don't need update strategy at Mapping level to change the type.
Example --
Let's create a simple mapping which will insert data into target table and at session level choose Treat Source Rows As option to "Update"
Considering your target as relational table, you need to select below options at target level in your session
1. Update As Update
2. Update As Insert
3. Update else Insert
Note -- if you do not select the Insert check box, and run the mapping like that, you will get below error as all records will get rejected.
Severity Timestamp Node Thread Message Code Message
ERROR 3/18/2014 7:15:47 PM node01_gogate WRITER_1_*_1 WRT_8118
ERROR: Target table [Countries2] does not allow UPDATE
Row # [0] in bad file
Below is the screen shot from infra help files.
If you run your mapping again, and since now you have all records present at target, now same record set will be updated.
What if you want to do insert/update logic using this method?
ReplyDelete-Insert if the primary key doesn't exist in target.
-Update if the primary key exists in target.
Please help with your Free Gyaan :).