how to keep certain columns in a table when import?

Posted on 2011-10-12
Last Modified: 2012-05-12
Have a need that only want to import  a few columns to a table and leave the other columns in the table untouched. Is it possible with impdp? For example,
Have a table T with 3 columns as follows:
col1 col2 col3

Want to import only columns col1 and col2 and keep col3 untouched.
If impdp can not do that, is there any better way to achieve it?

Question by:jl66
    LVL 14

    Assisted Solution

    To my knowledge, impdp may not work with selective columns

    However, if you export the data in flat file, you can make use of SQL LOADER


    Author Comment

    Thanks a lot for the info. Do any gurus have different opinion from ajexpert's conclusion?
    LVL 6

    Accepted Solution

    The way to export selective columns to a flat file is using UTL_FILE pl/sql package.
    or generating a spool file with the only two columns you need, even create a html file with the results easely

    SQL> set markup html on
    SQL> set echo off            
    SQL&gt; spool html_report.html
    SQL&gt; select sysdate from dual;
    <table border='1' width='90%' align='center' summary='Script output'>
    <th scope="col">
    SQL&gt; spool off
    SQL&gt; set markup html off

    Open in new window

    LVL 7

    Assisted Solution

    "keep col3 untouched" could mean that you wish to leave this column NULL or do you mean that there is existing data in col3 that needs to match with the new data being added to col1 and col2.

    In this case, using a temporary table to import col1 and col2 and then some PL/SQL routine that updates the col1 and col2 of your table doing the appropriate update on the rows with matching col3 (whatever that criteria is).

    Author Closing Comment

    Thanks a lot.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now