My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

Applies to:

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


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');

-- 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


#> 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


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.