Solved

Oracle SQL Insert Script to copy data between tables

Posted on 2016-09-27
8
68 Views
Last Modified: 2016-09-27
Hi All,

I have created a simple insert query to move the contents of table A into table B (there is some additions made in this process).

INSERT INTO TABLE B (Col A, Col B, Col C, Col D)
SELECT Col A, Col B, Col C,'Fixed' FROM TABLE A

Open in new window


However, this scrambles the order of the rows.  The order is important because it represents a parent-child relationship.  What do I need to change to maintain the row order?

Thanks,
Mark
0
Comment
Question by:SuperLight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41817999
>>What do I need to change to maintain the row order?

Add an order by in the select?
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 41818010
There is no order to rows in a table.  There is no order when they are retrieved from a table (only an ORDER BY guarantees that).  If you managed to get the rows into the table in some sort of order, then a simple update to one of the rows could easily change the order of the rows.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41818019
What johnsone said but I was creating a test case to show it.

Can you explain why you think insert order matters?  Are you getting some constraint error?

Here is a simple test showing the order doesn't matter:
drop table tab1 purge;
create table tab1
  (
     id      number primary key,
     child number references tab1(id)
  );

insert into tab1 values(1,1);
insert into tab1 values(2,1);
insert into tab1 values(3,2);
commit;



drop table tab2 purge;
create table tab2
  (
     id      number primary key,
     child number references tab2(id)
  );

insert into tab2 select * from tab1 order by child desc;
commit;

Open in new window

0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:SuperLight
ID: 41818022
Thanks for the clarification!  Easy points ;o)
0
 

Author Comment

by:SuperLight
ID: 41818024
For information, the table contains metadata that is used to build a hierarchy (Parent-Child format) therefore the order must be maintained otherwise the child will appear before the parent.

Thanks,
Mark
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41818028
>> therefore the order must be maintained otherwise the child will appear before the parent.

Insert order shouldn't matter with an insert as select.  Look at the example I posted.

Now, if you were doing individual inserts then yes, you need to make sure the parent exists before the child.

If you still believe order is important, then why did you select the post that said it didn't matter?
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 41818527
Try changing line 22 to

insert into tab2 select * from tab1 order by I'd, child; commit;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41818540
Ghunaima,

First:  This question is closed.
Second:  You commented on a test case showing that in this case, insert order doesn't matter.  The order by in the test case was as I intended it.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

739 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