How to Transfer the Data Between Tables With Different Structures? (Doc ID 1078211.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.1 - Release: 10.1 to 11.2
Information in this document applies to any platform.

Goal

You want to export the data from a source table and import into an existing target table. The structures of the two tables are different, like in the example below:

-- Structure at source
create table a_tab
(
   a_col number,
   b_col varchar2(100)
);

-- Populate source table
insert into a_tab values (1, 'Text 1');
insert into a_tab values (2, 'Text 2');
commit;

-- Structure at target
create table a_tab
(
   x_col number,
   y_col date,
   z_col varchar2(50)
);

If you export the table a_tab, either with original export utility:

#> exp test/test file=a_tab_exp.dmp tables=a_tab

or with DataPump export utility:

#> expdp test/test directory=dpu dumpfile=a_tab_expdp.dmp tables=a_tab

and then import the table at target, then you will get:

#> imp test/test file=a_tab_exp.dmp tables=a_tab ignore=y

...
. importing TEST's objects into TEST
. . importing table "A_TAB"
IMP-00058: ORACLE error 904 encountered
ORA-00904: "B_COL": invalid identifier


or:

#> impdp test/test directory=dpu dumpfile=a_tab_expdp.dmp tables=a_tab table_exists_action=append

...
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "TEST"."A_TAB" exists. Data will be appended to existing table
but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TEST"."A_TAB" failed to load/unload and is being s
kipped due to error:
ORA-02354: error in exporting/importing data
ORA-02373: Error parsing insert statement for table "TEST"."A_TAB".
ORA-00904: "B_COL": invalid identifier


Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms