Importing extra columns as null values
We are migrating our Oracle database to a new environment. There are some custom tables which are present in both the databases, but the new environment database has some extra columns in those tables. Could you please help me in using the export/import utility so that the extra columns are either imported as null values or are exported from the old environment as null values which will then be imported as is? Thanks.
There are likely two options here that will allow you to transfer data from the source to the target while preserving the extra columns on the target system. Using export/import will not allow you to preserve those columns in the target database. You can use a query option with export, but this only allows certain rows to be exported and does not allow only certain columns to be exported.
The first option involves creating a database link between the source and target databases, and then using the insert command to only insert the rows and columns you require. Here is an example:
INSERT INTO emp (empid, ename, email)
SELECT empid, ename, email
WHERE ... ;
Your second option would be SQL*Loader. You would have to read the rows out of the source tables into a format that SQL*Loader could read, but you would then have the option of using SQL*Loader to insert into the target or to append if rows already existed. For more information about SQL*Loader, review theOracle Database 10g Release 2 Utilities (10.2).
This was first published in December 2006