How to merge Oracle data 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:


Import from dumpFile.db1 into db1 then import from dumpFile.db2 into db1.
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?
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.