When creating a new table with SQL insert into statements, can you reorder the data before the table is created?

Question

For example, I have

Select ID, Name, Place
Into ##Temp_Table
From Table1

Select ID from ##Temp_Table

This returns a list of IDs roughly in no order at all

2,35,47,23,1,15 ... you get the point. If I add Order By ID then I get them in the correct ascending order.

Is there a way to make them ordered correctly BEFORE the insert into statement so that when I

Select ID from ##Temp_Table

I get the ascending order of IDs?

If I try

Select ID, Name, Place
Into ##Temp_Table
From Table1
Order By ID

Select ID from ##Temp_Table

I still get a jumbled mess.

Thanks!

Add an index after you create the table? If you create a clustered primary key, you'll get them out in proper order without an ORDER BY. However, you should also be aware that ALL tables are considered to be unordered collections, and if you really want order, you should never assume and always explicitly ORDER BY. — pmbAustin, May 04 '17 at 18:20
@pmbAustin with a clustered PK you'll *probably* get the records in the proper order without `ORDER BY` but it's still not guaranteed. — alroc, May 06 '17 at 13:34

score 2 · Answer 1 · edited May 23 '17 at 12:18

First, order of results is never guaranteed unless an order by is specified.

Second, if you create a unique clustered index on Id you will probably get what you want.

Select ID, Name, Place
Into ##Temp_Table
From Table1

create unique clustered index uix_##temp_table on ##temp_table (id)

Select ID from ##Temp_Table

For more, see this answer by Marc_S: https://stackoverflow.com/a/20050403/2333499

When creating a new table with SQL insert into statements, can you reorder the data before the table is created?

1 Answers1