• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3196
  • Last Modified:

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.
0
Joalkat
Asked:
Joalkat
  • 2
  • 2
1 Solution
 
ajexpertCommented:
There you go
INSERT INTO TABLEB (col1,col2,col3) SELECT * FROM TABLEA

Open in new window

0
 
JoalkatAuthor Commented:
Great!  Answered my question.
0
 
ajexpertCommented:
Points Please :)
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
JoalkatAuthor Commented:
I'll shoot you the 50 points once I understand how to release.  Thanks for your patience.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now