Order by not working when insert in temp table

Question

I have a query, and when I execute it in SQL Server 2012, the ORDER BY clause is not working. Please help me in this. Regards.

DECLARE @Data table (Id int identity(1,1), SKU varchar(10), QtyRec int,Expiry date,Rec date)
DECLARE @Qty int = 20

INSERT @Data 
VALUES
    ('001A', 5 ,'2017-01-15','2015-11-14'),
    ('001A', 8 ,'2017-01-10','2015-11-14'),
    ('001A', 6 ,'2015-12-15','2015-11-15'),
    ('001A', 25,'2016-01-01','2015-11-16'),
    ('001A', 9 ,'2015-12-20','2015-11-17');

SELECT * 
INTO #temp 
FROM @Data 
ORDER BY Id DESC

SELECT * 
FROM #temp

Answer 1

SQL tables represent unordered sets.

When you SELECT from a table, then the results are unordered. The one exception is when you use an ORDER BY in the outer query. So, include an ORDER BY and the results will be in order.

EDIT:

You can eliminate the work for the sort by introducing a clustered primary key.

create table #temp (
    Id int identity(1,1) primary key clustered, 
    SKU varchar(10),
    QtyRec int,
    Expiry date,
    Rec date
);

Then when you do:

insert into #temp(SKU, QtyRec, Expiry, Rec)
    select SKU, QtyRec, Expiry, Rec
    from @Data
    order by id;

The clustered primary key in #temp is guaranteed to be in the order specified by the order by. Then the query:

select *
from #temp
order by id;

will return the results in order, using the clustered index. No sort will be needed.

Answer 2

A SELECT ... INTO clause will help reach your expected output. I usually use temp tables in this way along with a column with a Row number using the ROW_NUMBER() function. It automatically orders the selected rows to the temp table. Or more simply, you can use the ORDER BY clause.