examples for update control in target table options

[#1]

hi all,

i am trying to understand update control options in target table -> options.

specifically these concepts: use input keys, update key columns, autocorrect load(allow merge)

i got the definitions but it has not been possible to build a sample job implementing these concepts.

can anyone provide examples for this?

example:

use input keys: as per the definition if the target table has no keys, it should consider the keys of the preceding data [from query transform etc]

i prepared a sample dataflow : source [empid, location] -> query transform [primary key : empid ]  -> target [has no keys]

run 1 : input : empid = 1 location='loc1'   output : empid = 1 location='loc1'

run 2 : input : empid = 1 location ='loc2'  output : empid = 1 location='loc1'  and  empid = 1 location='loc2'

[new record has got appended instead of getting updated]

could anyone spot what is wrong here?

[Former Member · Accepted solution]

Hi,

In the above case, you have set

Input Primary key -Yes and Autocorrect Load-No

It is using the primary key which is set by query transform.

But nowhere you have specified it to update, so it will get inserted as a new row.

Solution:

Set Autocorrect Load-Yes

This will update the record on the based off input primary key.

Result:

There will be only one record with the updated value.

E.g)

run 1 : input : empid = 1 location='loc1'   output : empid = 1 location='loc1'

run 2 : input : empid = 1 location ='loc2'  output : empid = 1 location='loc2' and  empid = 1 location='loc2'

[Former Member]

Hi Swetha,

Input keys-If the target table contains no primary key, this option enables the software to use the primary keys from the input.

In your job, so you changed the flag 'Use input keys' to yes and also 'Update key columns' to yes?

Auto correct:what is the issue that you are facing on this option?
It is upsert method basically and typically used for data recovery operations.Not staight forward solution to handle duplicates.