How to INSERT records from Data Link Tables using NOT EXISTS

Question

I have 4 remote locations and oracle express database installed. The locations create some transactions there and I want to run a job/procedure once in a day to insert the new records into a central table at our head office. The table structure of the head office and remote table is exactly same.

I am running the below query but it doesn't insert any record. I don't want to use materialized views or drop the head office table every day and create it again.

INSERT INTO CENTRAL_DATA
   SELECT   *
     FROM   (SELECT   LOCATION_ID,
                      INVOICE_ID,
                      INVOICE_DATE,
                      ANALYSIS_1,
                      ANALYSIS_2
               FROM   INVOICEDATA@LOC1
             UNION ALL
             SELECT   LOCATION_ID,
                      INVOICE_ID,
                      INVOICE_DATE,
                      ANALYSIS_1,
                      ANALYSIS_2
               FROM   INVOICEDATA@LOC2
             UNION ALL
             SELECT   LOCATION_ID,
                      INVOICE_ID,
                      INVOICE_DATE,
                      ANALYSIS_1,
                      ANALYSIS_2
               FROM   INVOICEDATA@LOC3)
    WHERE   NOT EXISTS (SELECT   * FROM CENTRAL_DATA)

Any help will be highly appreciated. Thanks.

score 1 · Accepted Answer · edited May 23 '17 at 12:25

Your query does not insert anything when there exists a record in the CENTRAL_DATA table. You are missing a selection criteria. What that criterium is I do not know. Maybe you want to insert only when there is no record for a specific location? The query would then be:

INSERT INTO CENTRAL_DATA
   SELECT   *
     FROM   (SELECT   LOCATION_ID,
                      INVOICE_ID,
                      INVOICE_DATE,
                      ANALYSIS_1,
                      ANALYSIS_2
               FROM   INVOICEDATA@LOC1
             UNION ALL
             SELECT   LOCATION_ID,
                      INVOICE_ID,
                      INVOICE_DATE,
                      ANALYSIS_1,
                      ANALYSIS_2
               FROM   INVOICEDATA@LOC2
             UNION ALL
             SELECT   LOCATION_ID,
                      INVOICE_ID,
                      INVOICE_DATE,
                      ANALYSIS_1,
                      ANALYSIS_2
               FROM   INVOICEDATA@LOC3) I
    WHERE NOT EXISTS (SELECT 1 
                      FROM CENTRAL_DATA d
                      where d.location_id=i.location_id)

However, see the comments on why this is not always a good idea: Oracle: how to INSERT if a row doesn't exist

Thanks for the answer. Records will be already there in the CENTRAL_DATA table related to each location, I just want to insert whichever is not available. My unique identifiers will be multiple in that case. — user3625561, Feb 14 '17 at 13:00
it worked but it took 5 minutes for 8 remote locations and 143 new records. As it will run daily once, I am not that concerned about the run time. If any quick method is available, i am open to it. Thanks again. — user3625561, Feb 14 '17 at 13:10
Maybe the union over three locations is slowing it down. Try doing them one by one. — Rene, Feb 14 '17 at 13:13

How to INSERT records from Data Link Tables using NOT EXISTS

1 Answers1