Insert into Select does not insert the same data order correctly

Question

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

Figure 2: Select command for the destination table

What can I do to solve this problem?

Answer 1

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;