Insert based on select
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Insert based on select

  1. #1
    Join Date
    Jul 2001
    Posts
    334
    Hi all,
    When we insert a table based on select, then why it change the order of the rows in the new table.

    For some reason we do not want to change the rows order

    e.g
    insert into new_table
    (cola,colb,colc)
    select col1,col2,col3 from old_table
    where -------
    and -------;

    Thanks

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Data in a relational structure has no inherent order.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jul 2001
    Posts
    334
    Thanks Marist,
    Mean there is no way we can get the same order as of query?

    Appreciate your help.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    use order by if you insist

  5. #5
    Join Date
    Jul 2001
    Posts
    334
    Thanks,

    I am inserting new-table in D2K Reports BeforReport and I have tried to use Order by but it gives error else it works fine.

    Any Idea.
    Thanks..

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    As Jeff said, there is no inherent row order in tables in Oracle.

    It does not matter what order you *try* to insert records into a table, they may or may not actually get inserted in that order. Further, when you try to SELECT these same records back out, you may or may not get them in the order that they exist in the table.

    The only way to guarantee the order of rows inside the table itself is to make it an index-organized table. Even then, the order of the records once SELECTed cannot be guaranteed.

    Simply put, you *need* an ORDER BY in any SELECT if you care about the order. Period.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width