I am trying to insert into a table using a select statement:
Insert into Table Select Field1, Field2, Field3 From Table2 Order by Field2
However, the records are not sort when they are inserted into the table. Is there anyway to insert with the row order intact?
Order By is only for the result set of your query; it has nothing to do with the physical arrangement of records in your second destination table. How the records are physically placed in the table's pages depends on what its clustered index is. If you truly want to have the records physically sorted by Field2, then Field2 should be defined as the clustered index.
You should then check if Field2 is a random or sequential value. Because if it is a random value and records have a high chance of being inserted inbetween each other, you stand a high chance of page segmentation which hurts I/O writes due to the among of re-arrangement writes SQL Server has to do to keep the sorted order among all the pages. A sequential (ever-increasing) Field2 value will minimise this by simply creating a new page at the end of the table.
Consider carefully if you will truly benefit from making Field2 the clustered index. Usually, using Field2 in a non-clustered index should suffice with an Order By clause when querying the second table.
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/23/644607.aspx
The melody of logic will always play out the truth. ~ Narumi Ayumu, Spiral