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?

  • If you can create a DBLink between the two servers you can do an insert ... select ... from table1@dblink probably easier than using DataPump. But that will only work if there is a network connection between the two servers.
    – user1822
    Commented Jan 9, 2015 at 15:55
  • Additionally to what a_horse_with_no_name said you can 1. import the table to a different name 2. add the missing columns (I'm not 100% sure if you need this step) 3. Then use INSERT INTO ... SELECT * FROM ... to finish the job. And yes, dblink would be much less work.
    – o0x258
    Commented Jan 9, 2015 at 16:01
  • @a_horse_with_no_name, I like that suggestion. As you mention, though, it may not be suitable for every situation.
    – pandoh
    Commented Jan 12, 2015 at 15:19
  • Take a look at oracle-base.com/articles/11g/… perhaps you can use this feature to help import those extra columns. If those extra columns are static (or derivable), it is pretty easy using this method. Take a look, since we don't know your exact requirements about those extra columns, cannot offer more.
    – Raj
    Commented Jan 13, 2015 at 13:59

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

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.