SELECT * INTO does not retain the ORDER BY clause in SA 16?

[former_SQLA_member1694869]

Execute the following SQL. When executed in SA 16, the sortorder is not retained. Is there a work-around to retain the sort order? It needs to be sorted in the SELECT INTO, because the sorting column not will be created in the table.

CREATE TABLE #MyTable(Name VARCHAR(50), SortOrder INT)
INSERT INTO #MyTable SELECT 'b', 2 UNION ALL SELECT 'c', 3 UNION ALL SELECT 'a', 1 UNION ALL SELECT 'e', 5 UNION ALL SELECT 'd', 4

SELECT * INTO #Result FROM #MyTable ORDER BY SortOrder

SELECT * FROM #Result

DROP TABLE #MyTable
DROP TABLE #Result

[MarkCulp · Accepted solution]

In a relational database the order is not guaranteed unless you explicitly state the order. Your second SELECT statement does not contain an ORDER BY clause so the returned order is allowed to be any order.

If you want to get a specific order back you can add an "bymyord INT DEFAULT AUTOINCREMENT" column to the first SELECT statement and then add an "ORDER BY bymyord" in the second SELECT statement.

HTH

[Breck_Carter · Accepted solution]

The #Result table already contains a SortOrder column, no need (in this case) for an autoincrement...

...but your suggestion is worthwhile in many other cases where the original sort order is lost on insert; e.g., when a text file is loaded into a VARCHAR table.