Order while inserting data from one table to another

  • I’m trying to insert data from 1 table to another in a specific order.

    Lets say in table1, I have data saying 4,2,3,1

    Now I need to insert data from table1 to table2 in a specific order say 1,2,3,4

    So I’m writing query: insert table2(id) select id from table1 order by id.

    I’m assuming data should go like 1,2,3,4 but its not the case always. Everytime it goes in different order say 1,3,2,4 or 1,2,3,4 or 4,3,2,1 or 1,3,4,2

    Could you please suggest on that,

  • Hi

    SQL does not store data as an ordered set, there is no way to guarantee the order of an INSERT even using an ORDER BY

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • Doesn't make much sense what you're trying to do here.

    If you want to display your data in a specific order just use order by in yor select. As simple as that.

    Or create a clustered index on your destination table.

  • Mahesh Gupta-325917 (6/28/2012)


    I’m trying to insert data from 1 table to another in a specific order.

    Lets say in table1, I have data saying 4,2,3,1

    Now I need to insert data from table1 to table2 in a specific order say 1,2,3,4

    So I’m writing query: insert table2(id) select id from table1 order by id.

    I’m assuming data should go like 1,2,3,4 but its not the case always. Everytime it goes in different order say 1,3,2,4 or 1,2,3,4 or 4,3,2,1 or 1,3,4,2

    Could you please suggest on that,

    Create a Clustered Index in table2 on column id

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • rhythmk (6/28/2012)


    Mahesh Gupta-325917 (6/28/2012)


    I’m trying to insert data from 1 table to another in a specific order.

    Lets say in table1, I have data saying 4,2,3,1

    Now I need to insert data from table1 to table2 in a specific order say 1,2,3,4

    So I’m writing query: insert table2(id) select id from table1 order by id.

    I’m assuming data should go like 1,2,3,4 but its not the case always. Everytime it goes in different order say 1,3,2,4 or 1,2,3,4 or 4,3,2,1 or 1,3,4,2

    Could you please suggest on that,

    Create a Clustered Index in table2 on column id

    Be careful here. If I read what it sounds like you are saying, you are suggesting to create this clustered index so the rows in table2 will be "ordered" correctly? There are times when you really do want an order by for an insert (if there is an identity on the target). However sql will still store this data in what it finds the most convenient. If you want your data from table2 to be returned in a certain order there is 1 and only 1 way to achieve this...use an order by when you pull the data out.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (6/28/2012)


    rhythmk (6/28/2012)


    Mahesh Gupta-325917 (6/28/2012)


    I’m trying to insert data from 1 table to another in a specific order.

    Lets say in table1, I have data saying 4,2,3,1

    Now I need to insert data from table1 to table2 in a specific order say 1,2,3,4

    So I’m writing query: insert table2(id) select id from table1 order by id.

    I’m assuming data should go like 1,2,3,4 but its not the case always. Everytime it goes in different order say 1,3,2,4 or 1,2,3,4 or 4,3,2,1 or 1,3,4,2

    Could you please suggest on that,

    Create a Clustered Index in table2 on column id

    Be careful here. If I read what it sounds like you are saying, you are suggesting to create this clustered index so the rows in table2 will be "ordered" correctly? There are times when you really do want an order by for an insert (if there is an identity on the target). However sql will still store this data in what it finds the most convenient. If you want your data from table2 to be returned in a certain order there is 1 and only 1 way to achieve this...use an order by when you pull the data out.

    We got very good point here to discuss.I agree with Sean in case there are lot of DML operations on the table but if it is not the case like the table is basically used for Reporting purpose (it may be the case as Mahesh is getting the data from one table to another) then order by clause may cost him a lot to fetch many records.While a clustered index will certainly reduce the execution time for report query.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • The clustered index will still not guarantee the order of the data when output via a select statement.

    The only way to guarantee the order of the data is to use an ORDER BY clause.

  • Lynn Pettis (6/29/2012)


    The clustered index will still not guarantee the order of the data when output via a select statement.

    The only way to guarantee the order of the data is to use an ORDER BY clause.

    I appreciate your and Sean's response.

    The below article helped me to understand how things work in such a case.

    "SQL Server will make effort to create it in a contiguous manner ... but there are no guarantees..."

    http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-i

  • I am not sure that I understand why you would want to control the order of insert. If there were a column in table 1 that you wanted to control the table 2 order you could do that with an index. I would agree that the best way to control the order of a result set is to use and order by on the select from table 2.

    SQL is going to attempt to accomplish the insert into the b table as set of rows. If you must subvert this you could write a cursor that steps thru table 1 and to single inserts into table 2. While this may be the worst possible way from a performance perspective it would guarantee the insert order.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply