Using Oracle Datapump when target table has additional column

datapumporacle

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?

Best Answer

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.

Related Solutions

How to manage column order when using PL/SQL’s %ROWTYPE records

If the definition of toptab will potentially be different in different environments (which is, of course, a separate issue that needs addressing), your options would generally be to either create an object type that does have a defined order and BULK COLLECT into that or to define a different collection for each column that you specify as the targets of your BULK COLLECT.

For the object approach, you'd define an object

CREATE TYPE toptab_like_object
    AS OBJECT (
  col1 number,
  col2 number,
  ...
  colN number
 );

and then use that object rather than the %ROWTYPE in your code

DECLARE
  TYPE t_toptab_list IS TABLE OF toptab_like_object;
  l_data t_toptab_list;
BEGIN
  SELECT toptab_like_object( ... ) -- many columns, with some manipulation, etc.
  BULK COLLECT INTO l_data
  FROM a,b,c,d
  WHERE ... -- join and filter, etc.
END;

You could also define a different collection for each column (you could probably define a much smaller number of collection types)

DECLARE
  TYPE t_col1_list IS TABLE OF toptab.col1%TYPE;
  TYPE t_col2_list IS TABLE OF toptab.col2%TYPE;
  ...
  TYPE t_colN_list IS TABLE OF toptab.colN%TYPE;

  l_col1 t_col1_list;
  l_col2 t_col2_list;
  ...
  l_colN t_colN_list;
BEGIN
  SELECT ... -- many columns, with some manipulation, etc.
  BULK COLLECT INTO l_col1, l_col2, ... l_colN
  FROM a,b,c,d
  WHERE ... -- join and filter, etc.
END;

Silent truncation of last character when using SSIS with Oracle

It looks like the problem was caused by a data flow task where I had two different data flows running in parallel. The same source tables were involved in both data sources but with different destinations. When I broke the task up into two tasks running one after the other, the truncation problem seems to have gone away.

Best Answer

Related Solutions

How to manage column order when using PL/SQL’s %ROWTYPE records

Silent truncation of last character when using SSIS with Oracle

Related Question