Joalkat
asked on
Oracle Insert from One Table to Another Table, Same Schema
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.
ASKER
Great! Answered my question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
I'll shoot you the 50 points once I understand how to release. Thanks for your patience.
Open in new window