Using Oracle Datapump when target table has additional column

Question

Table 1 has 5 columns. Table 2 has the same 5 columns, plus an additional non-nullable column. I would like to use datapump to copy the data from Table 1 to Table 2. In Sql Server, I can use bcp and specify a query to be used on export, in which I can "SELECT *, 'New Data'" from Table 1. This has the effect of putting the string 'New Data' into my new column in Table 2 when I import.

How would I accomplish this using Oracle's Datapump? From what I've read, I can only specify a WHERE clause, which means I can't add 'New Data' to my new column.

So far the only idea I've come up with is to disable the not null constraint on the new column in Table 2, datapump the data over from Table 1, and then update all rows to add 'New Data' into the new column. Is that the only option I have?

Answer 1

Just import your data into a a staging table at the target database. Then write a SQL staement to copy the rows from the staging table into the target table while giving the two new columns an explicit value. So something like this;

The following syntax should work on 11g

impdp user/password directory=import_directory dumpfile=data.dmp  REMAP_TABLE=schema.source_table_name:staging_table_name

Then, simply do something like this;

INSERT INTO target_table (column1, column2, column3, column4, column5)
SELECT column1, column2, column3, 'New Data', 'New Data2'
FROM staging_table_name 

After which you can drop the staging table.