Preserving ORDER BY in SELECT INTO

I know this is a bit old, but I needed to do something similar. I wanted to insert the contents of one table into another, but in a random order. I found that I could do this by using select top n and order by newid(). Without the 'top n', order was not preserved and the second table had rows in the same order as the first. However, with 'top n', the order (random in my case) was preserved. I used a value of 'n' that was greater than the number of rows. So my query was along the lines of:

insert Table2 (T2Col1, T2Col2)
  select top 10000 T1Col1, T1Col2
  from Table1
  order by newid()

Just add top to your sql with a number that is greater than the actual number of rows:

SELECT top 25000 * 
into spx_copy
  from SPX
  order by date

What for?

Point is – data in a table is not ordered. In SQL Server the intrinsic storage order of a table is that of the (if defined) clustered index.

The order in which data is inserted is basically "irrelevant". It is forgotten the moment the data is written into the table.

As such, nothing is gained, even if you get this stuff. If you need an order when dealing with data, you HAVE To put an order by clause on the select that gets it. Anything else is random - i.e. the order you et data is not determined and may change.

So it makes no sense to have a specific order on the insert as you try to achieve.

SQL 101: sets have no order.