0

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. – a_horse_with_no_name Jan 9 '15 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. – ora-600 Jan 9 '15 at 16:01
  • @a_horse_with_no_name, I like that suggestion. As you mention, though, it may not be suitable for every situation. – jubil Jan 12 '15 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 Jan 13 '15 at 13:59
3

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, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.