6

I used insert into command in SQL Server 2014, but does not insert as the same order of the data.

It shows the same number of rows but it not the same data order as you seen in the figures below.

The insert command is:

insert into [test].[dbo].[HöjdKortvågVänster] ([Höjd kortvåg vänster (null)]) select [Höjd kortvåg vänster (null)] from [test].[dbo].[test111]

Figure 1: Select command for the source table

enter image description here

Figure 2: Select command for the destination table

enter image description here

What can I do to solve this problem?

Yamur
  • 339
  • 6
  • 20
  • 1
    Your data is stored and ordered physically depending on your clustered index definition. The `ORDER BY` clause is ignored during the `INSERT` operation. – gotqn Jul 30 '15 at 10:48
  • 1
    Thank you @gotqn. Then, What should I do to insert it as the same order? – Yamur Jul 30 '15 at 10:50
  • @gotqn Order By is not ignored during Insert. He just didn't use it at all. – AdamL Jul 30 '15 at 10:51
  • First, do you need the data to be stored and ordered depending on your insert statements. You can always use `ORDER BY` clause to show it in a desired way. If yes, then check if there is a clustered index on your table and show your table structure as well. – gotqn Jul 30 '15 at 10:52
  • @AdamŁuniewski Even he is not directly using the `ORDER BY` clause, the data is ordered by the engine in some way and this ordering is not relevant to the physical ordering of the data. – gotqn Jul 30 '15 at 10:53
  • 1
    someone asking this question:http://stackoverflow.com/questions/14424929/preserving-order-by-in-select-into "Preserving ORDER BY in SELECT INTO" –  Jul 30 '15 at 10:53
  • Thanks @AdamŁuniewski and @gordatron, but still there is no solution for it. While, I need to have an the same order as the sources. Since I would like to have a independent table with specific calculation for each value in the same order. `ORDER BY` does not give me solution. – Yamur Jul 30 '15 at 10:58
  • @gotqn Yes, this is correct, and it means that you can't expect those two Selects return data ordered the same way. But what I'm saying, is that Order By is not being ignored, meaning that the data will be sorted prior to insert. Wheather it makes sense to sort it first is another story, but there are situations where this is beneficial, especially when there's a clustered index on the table (consider fragmentation and page splits). – AdamL Jul 30 '15 at 11:04
  • @AdamŁuniewski - It *is* ignored during `INSERT` except for some specific cases otherwise since SQL server 2012 http://stackoverflow.com/q/11222043/73226 – Martin Smith Jul 30 '15 at 18:20
  • 2
    @gotqn - Data isn't always stored physically in clustered index key order http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean/24470091#24470091 – Martin Smith Jul 30 '15 at 18:22

1 Answers1

6

SQL result sets are unordered, unless you have an order by. SQL tables are unordered.

However, SQL Server does provide at least one method to do what you want. If you use an order by in a table with an identity column and the select has an order by, then the identity is incremented appropriately.

So, one way of doing what you want is to have such as column in [HöjdKortvågVänster]:

id int not null identity(1, 1) primary key

insert into [test].[dbo].[HöjdKortvågVänster]([Höjd kortvåg vänster (null)]) 
    select [Höjd kortvåg vänster (null)]
    from [test].[dbo].[test111]
    order by <appropriate column here>;

And then when you query the table, remember the order by:

select *
from [test].[dbo].[HöjdKortvågVänster]
order by id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Do you think I need the ID in the source and destination tables in both? – Yamur Jul 30 '15 at 11:03
  • @YAcaCsh, you will need some source column to `ORDER BY` in the `INSERT...SELECT...ORDER BY`, which does not necessarily have to be an `IDENTITY`. The destination will need an `IDENTITY` if you use that the second `ORDER BY`. If you have the same column in both source and destination, maybe you don't need `IDENTITY` at all. – Dan Guzman Jul 30 '15 at 11:28
  • The destination needs an `identity` column. You need an `order by` in both the `insert` and in the `select` after you have finished the insert. – Gordon Linoff Jul 30 '15 at 12:13