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.
Missing Key Columns in Update SQL - write method Upsert
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 19
- Joined: Fri May 14, 2010 3:54 pm
Missing Key Columns in Update SQL - write method Upsert
Rajesekhar Potteti
-
- Premium Member
- Posts: 72
- Joined: Mon Jul 06, 2009 9:34 pm
- Location: Sydney
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.
Prakash Dasika
ETL Consultant
Sydney
Australia
ETL Consultant
Sydney
Australia
-
- Participant
- Posts: 19
- Joined: Fri May 14, 2010 3:54 pm
-
- Premium Member
- Posts: 72
- Joined: Mon Jul 06, 2009 9:34 pm
- Location: Sydney
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.
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.
Prakash Dasika
ETL Consultant
Sydney
Australia
ETL Consultant
Sydney
Australia