I am trying to create a temp table and my query reads as
Create table temp.Customer_All as (select ID, Name, ... from Customer... order by ID) with data;
However, order by is not allowed in subquery. Does anyone know how to obtain an answer set with ID in ascending order when creating a table?
use @@identity for automatic row id increase
It's a misconception that the rows in a table are ordered.
It's when you query a table that you can specify in which order you want the rows. You can have a Clustered index on a table, which in a way will actually order the datarows. But still, if you query the table without an ORDER BY clause, you'll get the rows in whatever order SQL Server finds most efficient.
So just go ahead creating the temp table, add an index to the ID column in the temp table and use ORDER BY ID when you query your temp table.
Regards ///Magnus
I am not used to the syntax in the question, because it smells like Oracle. With standard T-SQL the ordering can be achieved by using select into from syntax, so if the query in question is restated as
select * into temp.Customer_All
from Customer
order by ID;
then the rows will be inserted in the ID order. This does not guarantee though that they will be stored in the same order simply because the temp.Customer_All will end up being a heap table. However, assuming that the syntax in the question is valid in SQL Server 2008, it is easily possible to achieve the same effect (control the order in which the rows are inserted) by using a technique commonly referred to as a cheesy SQL (to allow the order by inside of the sub-select):
create table temp.Customer_All as
(
select top 100 percent
ID, Name, ...
from Customer...
order by ID
) with data;
Again, this will control the order in which the records are inserted which has nothing to do with how they end up actually stored as the temp.Customer_All is still going to be a heap table. On the other hand, altering the table by adding a clustered index on ID column will address the issue with logical ordering of the stored records, and this means that none of the nonsence with attempting to control the order in which the records are inserted is actually necessary.
Oleg
