I am trying to sort a database table and insert sorted rows into another table. However, when I query the new table, it still has unsorted data.
Create table #unsorted (ID int, dt date)
Insert into #unsorted values(1, '2019-01-01')
Insert into #unsorted values(2, '2018-12-15')
Insert into #unsorted values(3, '2017-01-01')
select * from #unsorted -- unsorted data as expected
Create table #sorted (ID int, dt date)
insert into #sorted
select ID, dt from #unsorted Order by dt asc
select * from #sorted -- It should be sorted by date but it is not.
Expected Result of #sorted table:
3 2017-01-01
2 2018-12-15
1 2019-01-01
Actual Result of #sorted table:
1 2019-01-01
2 2018-12-15
3 2017-01-01
How can I physically sort data in the table when inserting into another table?
You can't - the database will order a table's data however it likes when it comes to storing it (usually tables have a clustering key, and storage order is based on the value of the column(s) that form the key. If there is no clustering key, storage order is unpredictable) and even then storage order may have no bearing on output order depending on what operations the query carries out in its pipeline
Order the data when you query it
Choosing a suitable clustering key based on how you plan to use the table's data is something of an art in itself and something your DBA will ponder very carefully, wringing his hands at the devs that just lash a GUID primary key in and leave SQL Server to suffer :)