Solved

How to sort while inserting

Posted on 2006-06-25
7
246 Views
Last Modified: 2012-05-05
Hello experts,

I have a database app with many tables and stored procs. Some stored procedures do insert into tamporary tables. My insert statements also include select statements. When I select I use order by clause but after insert, the rows are not ordered by the column name in order by clause. Any idea?

Thanks
0
Comment
Question by:sunny012097
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16980168
if you query the temp tables without ORDER BY clause, they are never guaranteed to come out "ordered" by the select.
you have to apply a ORDER BY when selecting from the temp table also
0
 

Author Comment

by:sunny012097
ID: 16980210
Here is a sample query from a stored procedure:
I want rows to be ordered by SessionDate which correspons to day column in the report_montly_value table

            
      INSERT INTO report_monthly_value (day,time_scale,inv_cost_val,inv_retail_val)
                  SELECT ins.SessionDate, RIGHT(CONVERT(char(9), ins.SessionDate, 6), 6), SUM(ind.ItemCOST * ind.ItemQTY), SUM(ind.ItemRETAIL * ind.ItemQTY)
                  FROM Inv_Data as ind, Inv_Sessions as ins
                  WHERE ins.ClientID  = @client_id AND
                        ins.ID = ind.SessionID AND
                        ind.InventorySTATUS = 'POST'
                  GROUP BY ins.SessionDate
                  ORDER BY ins.SessionDate
0
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 16980236
sunny, don't worry about insert order, let SQL take care of that internally.

If you want to see the results ordered, then do this:
SELECT day,time_scale,inv_cost_val,inv_retail_val
FROM report_monthly_value
WHERE -- Whatever you need to filter on here
ORDER BY day

And just like angelIII posted, NEVER assume the order will be what you want, add an ORDER BY clause.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:sunny012097
ID: 16980244
I have some reports that use the data in tables and they down't pull the data sorted. I don't want to fix all the reports one by one.
is there any way that I can order the rows in a table with a command?
0
 

Author Comment

by:sunny012097
ID: 16980246
I want to sort the rows somehow without select...
0
 
LVL 26

Accepted Solution

by:
DireOrbAnt earned 500 total points
ID: 16980261
I guess if you make day the Clustered Index on report_monthly_value, it might order them properly.
As a plus, your queries, if based on date range, will run faster.

Now, you understand that this is not a good practice, might not work, or ordering might change at any time :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16980683
>>I want to sort the rows somehow without select...<<
As DireOrbAnt has pointed out already you can hope that using a Clustered Index will always return the results in the right order and that by the time that changes you have long since moved on to better pastures or you can do it right.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with SQL Server Polygon 2 32
SSRS 2013 - Overlapping reports 2 19
Help creating a spatial object in SQL Server 4 20
Inserting oldest record into new table. 5 22
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now