Q

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.

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
FROM emp@remote_db
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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close