none
Record Order issue RRS feed

  • Question

  • i have a table 'tableA' which has only one column 'ColName'.

    there is one temp table 'tableTemp' which has 5000 records.

    when I insert records from 'tableTemp' to 'tableA' using INSERT SELECT command. it does not maintain same order as 'tableTemp' has. but same I use SELECT INTO , it maintains same order as 'tableTemp' has.

    but SELECT INTO Creates the table. which is not possible to do on a daily basis inside a proc. also adding any columns in 'tableA' not permitted. 

    kindly suggest how can I insert data in 'tableA' from 'tableTemp' with same order.



    SQL Server DBA


    • Edited by Zeal DBA Wednesday, January 11, 2017 7:46 AM
    Wednesday, January 11, 2017 7:45 AM

All replies

  • It depends which Order you want to maintain and Later run SELECT. You can always use ORDER By during INSERT..SELCT as below:

    INSERT INTO tableA

    Select Col1 from tableTemp Order By Col1 (Either ASC or DESC)

    Thanks,


    Kindly mark the reply as answer if they help

    Wednesday, January 11, 2017 7:49 AM
  • kindly suggest how can I insert data in 'tableA' from 'tableTemp' with same order.



    SQL Server DBA



    Why is the order concern here? You can always write ORDER BY clause while selecting the data from tableA

    Cheers
    Vaibhav
    MCSA (SQL Server 2012)

    Wednesday, January 11, 2017 7:55 AM
  • Order is does not matter , it only does when you use SELECT statement and specify ORDER BY clause that only guarantee the proper sorting client see....

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 11, 2017 8:09 AM
    Answerer
  • INSERT INTO tableA

    Select Col1 from tableTemp Order By Col1 (Either ASC or DESC)

    above code i have tried also does not help ..


    SQL Server DBA

    Wednesday, January 11, 2017 9:11 AM
  • INSERT INTO tableA

    Select Col1 from tableTemp Order By Col1 (Either ASC or DESC)

    above code i have tried also does not help ..


    SQL Server DBA

    Can you paste you TSQL here?

    You need to also use ORDER By when select from the table after Insert.

    Thanks,


    Kindly mark the reply as answer if they help

    Wednesday, January 11, 2017 9:18 AM
  • actually table has single column which is varchar, order matters because, all records are composing a report type of presentation if they are in a order like 'tableTemp' which can be copied and pasted in txt file. but when insert to a 'tableA' report get messed up because of order mismatch. can not put ORDER BY clause also because does not have any other column like id which could define order. putting ORDER BY in the same Col also messing it up.


    SQL Server DBA

    Wednesday, January 11, 2017 9:18 AM
  • actually table has single column which is varchar, order matters because, all records are composing a report type of presentation if they are in a order like 'tableTemp' which can be copied and pasted in txt file. but when insert to a 'tableA' report get messed up because of order mismatch. can not put ORDER BY clause also because does not have any other column like id which could define order. putting ORDER BY in the same Col also messing it up.

    SQL Server DBA

    Wednesday, January 11, 2017 9:19 AM
  • You can add another extra column into this table only for the purpose of ORDER By. Insert into this in any order but while SELECT use this additional column for ordering the result set.. Let me know if you need help in doing that.

    Thanks,


    Kindly mark the reply as answer if they help

    Wednesday, January 11, 2017 9:27 AM
  • If so, you can add a column to the source table to determine the order and later on specify nit in the ORDER BY clause

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 11, 2017 9:34 AM
    Answerer
  • i tried that as well but as data come into destination table it get messed up again.. and i cant add any column in destination table as well.. can i use cursor wether that will help if insert record one by one.

    SQL Server DBA

    Wednesday, January 11, 2017 9:41 AM
  • Greetings,

    Your sort-order is defined by way you read the base-table.
    So unless all your indexes on your base-table have the same order, it might mis-match to your expectation.
    And this complexity increases when you join in other tables as it might not start reading from the table you expect.

    So how to fix this?
    - Take the hit for export and add "Order by" and include the sort-column in the table.
    - Take the hit and add "Order by" on the inserting query.
    - Take the hit and add an clustered index to ensure the data is sorted in your destination table.
    - Take the hit and ensure the indexes being used are sorted to your expectation.

    With kind regards,
    Sebastian

    Wednesday, January 11, 2017 9:45 AM
  • there are no indexes, keys, constraints  on the tables. only one column varchar on the table.

    I have come to know now what is the issue.

    there is a DELETE statement run every time on a destination table when proc executes.

    when i replaced DELETE with TRUNCATE statement, and now insertion is working fine getting the same order source and destination same order.

    can somebody explain, how DELETE and TRUNCATE, affects order of data insertion.


    SQL Server DBA

    Wednesday, January 11, 2017 11:03 AM
  • Can you post sample data + an expected result?

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 11, 2017 1:38 PM
    Answerer
  • Without an ORDER BY, the order of your rows in the select is out of your control. You cannot control the order of rows that are stored in your table reliably and rows are stored internally and decided by the engine when they are inserted.
    Wednesday, January 11, 2017 2:40 PM
    Moderator
  • I can not believe that there is no order being followed without ORDER BY clause, because it follows a specific pattern (same order every time) when we select data every time instead of random data in every select, and there is also some dependency on how we remove data from table using DELETE or TRUNCATE, because when we remove data from table using DELETE, it follows its own order at the time of select but when we TRUNCATE, it follows the order of source table at the time of select. 

    This is the behavior i can see now, but don't know the reason behind this.

     


    SQL Server DBA

    Thursday, January 12, 2017 5:17 AM
  • >>I can not believe that there is no order being >>>followed without ORDER BY clause,

    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/20/without-order-by-there-is-no-default-sort-order.aspx


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 12, 2017 5:22 AM
    Answerer