The only thing that order by on an insert is guaranteed to do is assign the values of an identity column if one exists. Your select has no order by, hence SQL is in no way required to return the data in any particular order.

It's not that one version is correct and the other is not, they're both correct, you're depending on behaviour that is not and never has been guaranteed. If you want an order in your returned results, you must put an order by on the outer-most select statement.

DECLARE @table TABLE

(

num INT

)

INSERT INTO @table

SELECT

Number

FROM

dbo.Number

SELECT *

FROM @table

ORDER BY

Number desc