How to merge Oracle data

oracleoracle-11g-r2

There are two schemas in Oracle11g. For example db1 and db2.

They have same structure of tables and different data to each other.

Now I want to merge data on db1 with data on db2 with no duplication.

But I can’t, duplication is happened.

I tried two different ways like bellow:

  1. Import from dumpFile.db1 into db1 then import from dumpFile.db2 into db1.

  2. After import data into both schemas and executo direct path insert.

    insert/*+ APPEND */ into db1.myTable (SELECT * FROM db2,myTable);.

Are there good way to merge without duplication?

Best Answer

Just add a WHERE clause to your select that eliminates the duplicates:

INSERT /*+ APPEND */ INTO db1.myTable (
   SELECT * FROM db2.myTable b WHERE NOT EXISTS 
      (SELECT 1 FROM db1.myTable a WHERE a.ID = b.ID)
   );

If you don't have a field like ID that is unique across the two tables, then you will need to compare more fields, perhaps all of them.

Related Question