Insert the rows of 'report_temp' which are not in 'report'?

Question

I've two tables :

report (reportID, VendorName, VendorID, MfgDate, PurchaseDate, etc.,)

In report table reportID is primary key.

report_temp has the same set of columns as report, but not any constraints.

I've to insert the rows from report_temp to report where the reportID is not the same. I've written as

  INSERT INTO report(reportID, VendorName, VendorID, MfgDate, PurchaseDate,...) 
      NOT (SELECT reportID, VendorName, VendorID, MfgDate, PurchaseDate,... 
           FROM report INNER JOIN report_temp USING (reportID, VendorName, 
                VendorID, MfgDate,PurchaseDate,...))

I've also tried with just reportID within USING clause, but I can't get it... If you tried anything like this share with me..

Answer 1

This will give you the set of reportIDs existing in both tables:

SELECT report_temp.reportID FROM report_temp, report WHERE report_temp.reportID=report.reportID

So this will give you the rows to insert:

SELECT * FROM report_temp WHERE reportID NOT IN (SELECT report_temp.reportID FROM report_temp, report WHERE report_temp.reportID=report.reportID)

Then just drop that into the report table:

INSERT INTO report SELECT * FROM report_temp WHERE reportID NOT IN (SELECT report_temp.reportID FROM report_temp, report WHERE report_temp.reportID=report.reportID)