locked
SQL-Order by clause not working RRS feed

  • Question

  • Hi Experts ,

    I am not able to insert Data from One Table to another using Order By Clause ,Below is the workAround I tried .

    CREATE TABLE TEMP1(ID INT ,DATES  DATE)

    INSERT INTO TEMP1
    SELECT 1,'2011-03-07' UNION ALL
    SELECT 2,'2010-03-06' UNION ALL
    SELECT 3,'2013-03-01' UNION ALL
    SELECT 4,'2009-03-21'

    SELECT * FROM TEMP1

    /*BELOW CODE DOES NOT GIVE THE REQUIRED OUTPUT I.E I DO NOT GET THE OUTPUT OF TEMP2 TABLE ORDER BY DATES*/

    SELECT * INTO TEMP2 FROM TEMP1 ORDER BY DATES
    SELECT * FROM TEMP2

    /*SECOND APPROACH ALSO DOES NOT WORK FOR SAME SCENARIO*/

    CREATE TABLE TEMP3 (ID INT,DATES DATE)
    INSERT INTO TEMP3
    SELECT ID,DATES FROM TEMP1 ORDER BY DATES

    SELECT * FROM TEMP3

    /*HOW DO I INSERT DATA FROM ONE TABLE TO OTHER WITH  ORDER BY ??*/

    Wednesday, March 6, 2013 1:11 PM

All replies

  • Your tables have no "order"  they are heaps.  If you had a clustered index on dates in Temp3 then the data would be effectively ordered in the table by dates, but you would still need to Select * from temp3 Order by dates to gurantee that they came out in any particular order.


    Chuck Pedretti | Magenic – North Region | magenic.com

    Wednesday, March 6, 2013 1:13 PM
  • Check this post,

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/aeb1053d-0dc2-4dc3-96f5-8b213fed9669

    http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx


    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you


    Wednesday, March 6, 2013 1:38 PM
  • It is not guaranteed  that the data will be inserted in specific order. Just use ORDER BY clause in SELECT statement.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, March 6, 2013 2:12 PM
    Answerer
  • Hi Uri

    Thanks for the Reply.

    I am already using Orderby Clause in my Select query i.e

    CREATE TABLE TEMP3 (ID INT,DATES DATE)
    INSERT INTO TEMP3
    SELECT ID,DATES FROM TEMP1 ORDER BY DATES

    SELECT * FROM TEMP3

    /*Copied from above thread*/

    But still Data in TEMP3 is same as TEMP1..it is not sort by date in Required order .

    Data in TEMP3 Table should look like

    ID            DATES
    4         2009-03-21
    2         2010-03-06
    1         2011-03-07
    3         2013-03-01 


    HOW DO I DO THAT ?
    • Edited by Rihan8585 Thursday, March 7, 2013 8:55 AM
    Thursday, March 7, 2013 8:52 AM
  • You won't able to sort the records when you insert, use a select query with order by after inserting into table

    I think when we create keys and index on columns, it gets sorted. Am i right ?

    "INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted "

    http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx


    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you



    Thursday, March 7, 2013 10:27 AM