Unanswered: Inserting records from one table to another ignoring duplicates
I have the following code that will insert records into table 1 from table 2 if it doesn't exist. However, I have found out through much testing that there can be duplicates in the source table (table 2). When a duplicate is found, I get a Unique constraint error and no records are inserted.
INSERT INTO ap_inv a (inv_num,po_num) SELECT inv_num,po_num FROM vn_inv v WHERE NOT EXISTS (SELECT null FROM ap_inv a WHERE a.inv_num=v.inv_num AND a.po_num=v.po_num)
What can I do to modify this statement to have duplicates ignored? I don't want to remove the duplicates.
I don't understand what you mean when you say 'I don't want to remove the duplicates'. Do you want to keep the duplicates (all the duplicates) or do you just want to keep one instance of each record, if the latter, which one would you want (first, last, the middle one, the second one, etc..) ?
Inserting records from one table to another ignoring duplicates
Yes, I want to keep all of what appears to be duplicates. I only want it to insert the inv_num and po_num into table 1 if it doesn't exist.
To explain better (I hope), the records are not really duplicates. However there can be several records in table 2 that have the same inv_num and po_num. Table 1 has only 1 instance of an inv_num and po_num per record (no duplicates).