CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Alina Dilshad
Alina Dilshad

Reputation: 193

Order by not working when insert in temp table

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

Upvotes: 14

Views: 33689

Answers (4)

T-Hoff_2022
T-Hoff_2022

Reputation: 21

You can keep you original logic and add an index after your temp table insertion completes.

SELECT
id 
INTO #temp
FROM @Data   

CREATE CLUSTERED INDEX c ON #temp(id);

The benefit of this solution is that if you were doing a union of two tables prior to the temp table insert, it would allow the data to be sorted like an order by clause. For Example:

SELECT
* 
INTO #temp
FROM (
     SELECT
     id
     ,date 
     FROM tableA
     
     union
     SELECT
     id
     ,date 
     FROM tableB) tableAB

CREATE CLUSTERED INDEX c ON #temp(id);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 22

Chanuka Naveen Koswatta
Chanuka Naveen Koswatta

Reputation: 75

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.

Upvotes: 2

Parimal Bhavsar
Parimal Bhavsar

Reputation: 1

You can use force option to use forcefully order execution. below is an example.

create table #temp (
    Id int,
    SKU varchar(10),
    QtyRec int,
    Expiry date,
    Rec date
);

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

Upvotes: -3

Related Questions