top of page

Forum Posts

Sunil Dhaul
Jan 04, 2025
In SQL Server Development
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))
1
1
26
Sunil Dhaul
May 04, 2022
In General Discussion
Share your thoughts. Feel free to add GIFs, videos, #hashtags and more to your posts and comments. Get started by commenting below.
1
0
5
Sunil Dhaul
May 04, 2022
In General Discussion
We'd love to get to know you better. Take a moment to say hi to the community in the comments.
0
0
3
Sunil Dhaul
May 04, 2022
In General Discussion
We want everyone to get the most out of this community, so we ask that you please read and follow these guidelines: Respect each other Keep posts relevant to the forum topic No spamming
0
0
4

Sunil Dhaul

Admin
More actions
bottom of page