Oracle SQL Insert Script to copy data between tables

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
SuperLightAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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
 
slightwv (䄆 Netminder) Commented:
>>What do I need to change to maintain the row order?

Add an order by in the select?
0
 
slightwv (䄆 Netminder) Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SuperLightAuthor Commented:
Thanks for the clarification!  Easy points ;o)
0
 
SuperLightAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>> 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
 
GhunaimaCommented:
Try changing line 22 to

insert into tab2 select * from tab1 order by I'd, child; commit;
0
 
slightwv (䄆 Netminder) Commented:
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
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.

All Courses

From novice to tech pro — start learning today.