Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Mar 2007
    Posts
    24

    Unanswered: INSERT INTO with ORDER BY

    Hi Everyone,

    I want to insert data in the table in the sorted order itself.


    my qry is :
    Code:
    INSERT INTO table
          SELECT col1, col2, col3 FROM table1
          UNION ALL
          SELECT col1, col2, col3 FROM table2
          ORDER BY col1, col2, col3
    The order by clause doesn't work in this manner.



    Can anyone help me.


    Thanks in advance.

    Riyaz

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I am surprised it does not work however this should:
    Code:
    INSERT INTO table SELECT * FROM (SELECT col1, col2, col3 FROM table1 UNION ALL SELECT col1, col2, col3 FROM table2) AS my_union ORDER BY col1, col2, col3
    BTW - you know that unless you have some sort of insert-order-sensitive column (an identity is the only type I can think of) then the order of insert is both irrelevent and impossible to derive from the data in the table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2007
    Posts
    24
    Quote Originally Posted by pootle flump
    I am surprised it does not work however this should:
    Code:
    INSERT INTO table SELECT * FROM (SELECT col1, col2, col3 FROM table1 UNION ALL SELECT col1, col2, col3 FROM table2) AS my_union ORDER BY col1, col2, col3
    BTW - you know that unless you have some sort of insert-order-sensitive column (an identity is the only type I can think of) then the order of insert is both irrelevent and impossible to derive from the data in the table?
    Hi,

    None of the columns; col1, col2, col3 is identity column.
    The data is not inserted in the sorted order. If I run the select command only on this data then I get the perfect results.

    But when I am inserting this data in the table it is not sorted.


    Thanks for your input.

    Riyaz

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I believe it was Brett that once said:
    The physical order of data in a databse has no meaning
    The order of insert is irrelevant!
    So just insert and then use a query with an ORDER BY statement to show the results in a specified order!

    You shouldn't be encouraging this sort of behavior Poots!
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    You shouldn't be encouraging this sort of behavior Poots!
    seconded

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    You shouldn't be encouraging this sort of behavior Poots!
    Quote Originally Posted by Rudy
    seconded

    Lol. In my defence I tried to make the point that the order of insertion is irrelevent (because we are talking sets ... yadda... yadda... yadda). The reason I mentioned identities is I have recently made use of inserting ordered data into a table with an identity column for some reconcilliation routines.

    But you are right - by even hinting that there might ever be a reason to insert ordered data into a table was very naughty. I consider my wrists slapped

    EDIT - BTW George -well done on making your first Kilo-post (whenever that was - last time I looked you werre on about 800 lol).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2007
    Posts
    24
    I can not use ORDER BY clause after inserting the data
    as I am sending data from new table to another stored procedure in which
    there is restriction for order by clause on incoming query.

    So data must be inserted in specified order only for further use.


    Thanks.


    Riyaz

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "I am sending data from new table"

    how, exactly, do you get the data out of the table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Posts
    24
    Quote Originally Posted by r937
    "I am sending data from new table"

    how, exactly, do you get the data out of the table?

    I'll explain:

    'Table1' is the table in which I am inserting the data.
    I am using 4 unions for selecting data from different tables.
    I want to insert this data in Table1 with sorting.

    After inserting the data I'll write:
    EXEC sp_test 'SELECT col1, col2, col3 FROM Table1'

    Data in Table1 must be sorted and I can not use ORDER BY clause in
    the above query with stored procedure due to its internal structure and I can not alter it.

    so data in Table1 must be sorted at the time of insertion.

    Thanks.

    Riyaz

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    EXEC sp_test 'SELECT col1, col2, col3 FROM Table1 ORDER BY col1, col2, col3'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    EXEC sp_test 'SELECT col1, col2, col3 FROM Table1 ORDER BY col1'
    EDIT: sniped by Rudy by 1 minute!
    George
    Home | Blog

  12. #12
    Join Date
    Mar 2007
    Posts
    24
    Quote Originally Posted by r937
    EXEC sp_test 'SELECT col1, col2, col3 FROM Table1 ORDER BY col1, col2, col3'
    Data in Table1 must be sorted and I can not use ORDER BY clause in
    the above query with stored procedure due to its internal structure and I can not alter it.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can views be ordered?
    George
    Home | Blog

  14. #14
    Join Date
    Mar 2007
    Posts
    24
    Quote Originally Posted by georgev
    Can views be ordered?
    using : TOP 100 PERCENT

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Yes, but it is a bad practice. If you order it one way, and a user wants it ordered differently, you get to sort the records twice.

Posting Permissions

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