CODEWITHSUNDEEP
sqlsql-server
Amol Vishwasrao
Amol Vishwasrao

Reputation: 1

Order by while inserting data into temp table.is not working

SELECT S.ColumnName,L.ColumnName ...
INTO #Tempdata
FROM [dbo].[M] M  
LEFT JOIN [dbo].[S] S ON S.PrimaryKey=M.FKey  
LEFT JOIN [dbo].[L] L ON S.FK=L.PK  
ORDER BY S.ColumnName

Upvotes: 0

Views: 639

Answers (2)

SQLpro
SQLpro

Reputation: 5131

Especially in tempdb, which is often stores data with multiple files, the rows inserted are splitted across the differents files. Retrieving the data using multiple threads (one thread by file to go faster) will give any combination of mingled subsets and give an impression of hasardous order of the rows.

As it has been said, there is no ways to have an ordered storage of rows in a table, and it is the reason why SQL have a ORDER BY clause !

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

SQL tables represent unordered sets. Period.

If you want to select the data in order, you need to use:

order by columnName

in the query used for the selecting the data.

SQL Server, though, does respect an ORDER BY in an INSERT in one important way. If you have an identity column, then the identity will follow the ORDER BY. This can be convenient. But to get ordered results (consistently) you need ORDER BY in the SELECT query.

Upvotes: 1

Related Questions