How to order row inserts into a table

In SQL Server, if we try to insert rows using a SELECT ORDER BY statement then the inserted rows are not 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.

The solution is to make the column on which you want to order the inserts as the primary key in the target table. Then if you insert using a SELECT statement the inserted rows in the target table would be ordered. 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.

Example code that orders row inserts in ascending order of Product

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);
--we want to order row inserts in ascending order of 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 order the inserts in 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))