Oracle Insert from One Table to Another Table, Same Schema

Posted on 2008-11-05
Last Modified: 2013-12-19
I want to copy rows from one TableA, with over a million records,  to anther TableB with identical column names, both in the same Schema.  TableB is completely emply, but has several more columns, so in effect, TableA is a subset of TableB.  How do I copy rows from Table A to the empty, Table B?  Thanks.
Question by:Joalkat
    LVL 14

    Expert Comment

    There you go

    Open in new window


    Author Comment

    Great!  Answered my question.
    LVL 14

    Accepted Solution

    Points Please :)
    LVL 34

    Expert Comment

    by:Mark Geerlings
    If performance is an issue, you may want to do a couple more things.

    First: "alter tableb nologging"
    Second: repeat that command for each index on tableb, for example: "alter index tableb_ix1 nologging"
    Third: do the insert like this:
    insert /*+APPEND */ into tableb (col1,col2,col3) SELECT * FROM TABLEA;

    This may allow the insert to be *MUCH* faster.  You should do a backup though of the tablespace for tableb and for any indexes on tableb after the load, since the load will not be recoverable otherwise.

    Author Closing Comment

    I'll shoot you the 50 points once I understand how to release.  Thanks for your patience.

    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

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    733 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

    20 Experts available now in Live!

    Get 1:1 Help Now