I have two identical tables. One I treat as stagging (stg) and second as production (pr). I get a daily file which I first fastload into the stg table and then use the merge command to push data over to pr. I only have one same unique key defined on both tables (contact_object_id). Durring the merge sql run I get error message of primary key
do you know what I am doing wrong.
Looks like, at least one of the contact_object_id in the source file (stg) is already present in your target table (PR) (may be from previous loads). And as you have defined contact_object_id as UPI, it is not allowing to insert it again and throwing an error.
Can you make sure that contact_object_id is unique in both stg and pr tables by taking count(*) and count(distinct contact_object_id) ? Because MULTISET and UPI together do not make sense to me.
I fixed the error message. Production table had null in the key field and merge was trying to insert another null instead of updating. I removed the null value from the production table and updated the merge command to select rows where contact_object_id is not null from the staging table.