asked on

Import data into a different table using Oracle imp utility

I have exported TableA to tableA.dmp.
I cann't drop or truncate table 'TableA', so I want do import to another table 'TableB'

In otherwords,
Is it possible to import data into a different table from an export file using oracle imp utility?

Thanks in advance.

ASKER CERTIFIED SOLUTION

Pierrick LOUBIER

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

Actually you can import from 'TableA' into 'TableB', but not so simply. You would have to rename TableA to something else (like "TableA_hidden"), then create a view named: TableA that is actually: "select * from TableB", then run the import, then drop the view, and rename "TableA_hidden" back to "TableA".

Or, you could do what ploubier suggested, that is import into a new TableA in a different schema, then copy the data from there to TableB.

johnsone

markgeer,

Is that even possible? You would have a vew called TableA, and then you would try to import TableA. Assuming you do not set IGNORE=Y on the import, it should fail because when you cannot have a view and a table with the same name in the same schema. You should get a ORA-00955. If you specify IGNORE=Y, then I would believe it would append the data in the import file to the table (assuming the same table structure of course).

Mark Geerlings

Yes, it is possible (or at least was with Oracle8 and Oracle9, I haven't tried it yet with Oracle10). Yes, you do need to include "ignore=y" on the import run. No, there is not both a table and a view with the same name in the same schema at the same time, because note what I had said: "You would have to rename TableA to something else (like 'TableA_hidden'), then create a view named: TableA ...".

johnsone

From your post, this is what I see the steps are:

rename table tablea to tablea_hidden;
create view tablea as select * from tablea_hidden;
imp

The last step (the import) would attempt to create another table called TableA, or am I missing something?

Mark Geerlings

The steps are:
1. rename tablea to tablea_hidden;
2. create view tablea as select * from tableb; -- note from "tableb"
3. run import with "ignore=Y" (so it will use the existing "tablea", even though that is a view)
4. drop the view (the data from "tablea" is now in "tableb")
5. rename tablea_hidden to tablea

johnsone

OK, now I understand, I thought you were creating the view so TableA would still be visible.

Is there really a need for the view though?

1. rename tablea to tablea_hidden;
2. run import
3. rename tablea to tableb;
4. rename tablea_hidden to tablea

Mark Geerlings

You are correct. That would also work.