Ordering of Row inserts into a table during INSERT statement

In SQL Server, if we try to INSERT rows using a SELECT ORDER BY statement as in the SQL mentioned below, then the inserted rows are not necessarily inserted in the same sequence as the rows returned by the SELECT ORDER BY statement. In other words there is no use of having an ORDER BY clause for the SELECT statement when inserting rows and its impossible to control the order of rows inserted.

The only control a developer has is over the default ordering when a SELECT statement is used without any ORDER BY clause, for the table in which rows have been inserted. This article explains how we control this default ordering.

INSERT INTO table1 (ProductId, Product) SELECT ProductId, Product FROM table2 order by Product;
SELECT ProductId, Product from table1;--this will not show rows ordered by Product, but ordered by the primary key or clustered index of table1 or in some unknown order if neither a primary key or a clustered index exists

APPROACH 1 (using Primary key or Clustered Index)

The solution is to make the column on which you want to have a default order the primary key in the target table. Then if you insert using a SELECT statement the inserted rows in the target table would have the default ordering by that column. Below examples use table variables to illustrate the solution, but the same concepts apply equally well to temp tables or regular tables in SQL Server.

DECLARE @table_source TABLE (ProductId INT ,Product VARCHAR(50));

INSERT INTO @table_source (ProductId, Product) VALUES (2, 'Blue Trousers'), (1, 'Red Trousers'), (7, 'Coral Sweater'), (3, 'White Shirt'), (4, 'Auburn Shirt'), (5, 'Blue Shirt'), (6, 'Red Shirt');

SELECT  * FROM @table_source;
DECLARE @table_target TABLE (ProductId INT ,Product VARCHAR(50) PRIMARY KEY);
--the above definition will result in inserted rows to be stored in ascending order of Product
--instead of specifying a primary key, you can create a clustered index
--for the target table on the column by which you want to sort rows inserted, if the target table is not a table variable 
--CREATE CLUSTERED INDEX IX_TargetTable_Product ON tableName (Product);

INSERT INTO @table_target (ProductId, Product) SELECT * FROM @table_source;
SELECT * FROM @table_target; --this will automatically return rows in ascending order of Product

What if we wanted to change the default order to a descending order of Product rather than an ascending order. The solution is the same as above except where the target table is declared/defined. The target table definition statement would be changed to the following.

DECLARE @table_target TABLE (ProductId INT ,Product VARCHAR(50) PRIMARY KEY CLUSTERED (Product DESC));
--instead of defining a primary key as above you could create a clustered index and get the same effect, provided the table is not a table variable
--(table variable cannot have a clustered index created by the below statement)
--CREATE CLUSTERED INDEX IX_TargetTable_Product ON tableName (Product DESC);

APPROACH 2 (using a dynamically generated INSERT query)

There is another way to control the default order after row inserts and that is by using a dynamically created INSERT query as shown below.

Note that the target table into which rows are inserted should not have a primary key or a clustered index else this approach will not work. The data from the source table is ordered using ROW_NUMBER( ) function in order to sequence by Product column.

CREATE TABLE #table_source  ( 
ProductId INT ,Product VARCHAR(50)
);
INSERT INTO #table_source (ProductId, Product) VALUES
(2, 'Blue Trousers'), (1, 'Red Trousers'), (7, 'Coral Sweater'), (3, 'White Shirt'), (4, 'Auburn Shirt'), (5, 'Blue Shirt'), (6, 'Red Shirt');

SELECT *  FROM #table_source;
--make sure there is no primary key defined in the target table
CREATE TABLE #table_target  (ProductId INT ,Product VARCHAR(50));

DECLARE @values NVARCHAR(MAX);

--create dynamically the VALUES part of the INSERT query 
--in the same order in which you want to insert the rows
--in the target table, as in statement below

SELECT @values= COALESCE(
@values + 
 ',(' + CAST(ts.ProductId AS VARCHAR(20)) +',''' + ts.Product +''')'
,' (' + CAST(ts.ProductId AS VARCHAR(20)) +',''' + ts.Product +''')' )  FROM #table_source ts ORDER BY Product;

PRINT @values;

DECLARE @insertQuery NVARCHAR(MAX);
SET @insertQuery = 'INSERT INTO #table_target (ProductId, Product) VALUES ' + @values;
PRINT @insertQuery;
EXECUTE sys.sp_executesql @insertQuery;
SELECT  FROM #table_target;--rows inserted are ordered automatically
DROP TABLE #table_source;
DROP TABLE #table_target;