INSERT then APPEND ONLY NEW DATA from TABLE_A into TABLE_B
Posted on 2008-10-11
First INSERT data into TABLE_B from TABLE_A. Subsquent INSERTs into TABLE_B should only APPEND new records from TABLE_A. This is the requirement.
I first created a new table TABLE_B. I have an existing table TABLE_A to which new records are constantly added.
After creating the new TABLE_B, I first inserted all the records from TABLE_A into TABLE_B.
I used the following INSERT statement in SQL*PLUS:
INSERT INTO TABLE_A(SELECT username, action, code
WHERE username NOT IN (SELECT * FROM X)
AND code NOT IN (SELECT * FROM Y))
ORDER BY 1, 2, 3;
Total records: 21 inserted into TABLE_B from TABLE_A.
New records were added to TABLE_A - three new records. Now the total records in TABLE_A is 24 but only THREE NEW RECORDS WERE ADDED.
I then did a second INSERT into TABLE_B from TABLE_A using the above INSERT statement a second time.
The second INSERT inserted all the rows from TABLE_A (TOTAL OF 24 RECORDS) into TABLE_B AND THE TOTAL RECORDS IN TABLE_B IS NOW 45 INSTEAD OF 24.
I did not get any ORACLE ERRORS after the second INSERT.
The requirement is that after the first INSERT into TABLE_B, only the new records from TABLE_A SHOULD BE APPENDED TO TABLE_B.
This is a high priority task. So please respond ASAP.