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?

  • Doesn't the Oracle MERGE statement work for you?
    – Mat
    Commented Nov 29, 2012 at 10:13
  • Actually I want merge any schema without ON condition or WHERE condition. I mean unless I know about table structure especially what primary key is, I want merge them if they have same structure. But As Leigh Riffel answered bellow ,I have to know about fields in each table. Commented Nov 30, 2012 at 4:17

2 Answers 2


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

INSERT /*+ APPEND */ INTO db1.myTable (
      (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.

  • Sounds good.I'll try it. Commented Nov 30, 2012 at 0:06

I use impdp command with CONTENT=DATA_ONLY DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS . It seems no duplication.

  • 1
    duplication is avoided by the primary/unique key contraints. so a record is a duplicate of another record of the same table exactly if they have the same value for the primary key or at least one unique key. that seems to be a reasonable definition if no surrogate keys are used.
    – miracle173
    Commented Dec 3, 2012 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.