Missing Key Columns in Update SQL - write method Upsert

[Rajesekhar]

Hi,

I have a table (ORDER_DETAIL) which has only one column (ORDER_NBR) and is not defined as Key in Database.

I wanted to do an upsert to the table.

when I do so the Update SQL is is showing Missing Key Columns.

UPDATE #$SchemaName#ORDER_DETAIL SET ORDER_NBR = ORCHESTRATE.ORDER_NBR WHERE (<<Missing key columns>>)

Can any one help me out, what might the issue? am I missing any thing?

Thanks, Raj.

[prakashdasika]

You cannot update a field unless a key is defined in the statement. If you have a single column i wonder how you can update it. i am assuming you might want to check if the value already is existing in the table and insert the new records. In that case try Change capture.

[Rajesekhar]

Hi Prakash,

Thanks for the response.

Yes your assumption is right. I wanted to check if the value already exists and insert newer records. Using upsert I thought of overwriting them, but didnt work.

I didnt get what is change capture. could you explain in detail.

Thanks, Raj.

[prakashdasika]

Change capture takes the before dataset (i.e. the existing table) and compares with the after dataset (i.e. the new records) and produces flags depending on the comparision. These flags are in a field named Change_code. The values of code represent the state of data like copy (already existing), Insert (new records) etc. Be sure to define the before and after datasets properly.

The second solution is to do a right outer join with the right being the new dataset and use a transformer to filter out the new ones and insert them directly in to the table.