Update table with new records

Question

This may be a basic Alteryx ETL question. I am trying to insert/update a basic table with 2 dimensions and one measure.

As you could see I  begin with a source table like above screenshot which I am trying to load into Target. Next time I may have a new record coming for these states (e.g. OH / Week 2 / 800) or there could be an update for an existing record (e.g. OH / Week 1 / 1350) . I need to handle both of these while loading the Target. My Database is Oracle.

I tried using the option Update : Insert if New. However it failed with error 'Primary Key required for Update option'.

I am not sure if that is the right option and how I can define a primary key to handle that with such a dataset. Thank you for the help.

Dataload Source.xlsx

AlterIT · Answer

Thanks for your comments. I tried Append Field Map both by 'Map By Name' and 'Custom Mapping' but both the time it throwing error 'Primary key required for update option'

mceleavey · Answer

Hi @AlterIT ,

Yes, the Update: Insert if New option is correct, but when connecting to SQL or Oracle, you must ensure ALL fields are mapped, not only the key fields, which makes it a bit confusing.

When outputting to a table you must ensure that you are populating every field.

Try that and let me know.

M.