How to store records so that they can be retrieved in the same record order in which they were stored as a RESULT SET based on a SELECT statement?

Posted on 2012-08-29
Last Modified: 2012-08-30
How would you modify the following SQL SELECT Statement using SQL Server 2005, so that the Result Set is saved into a temp table and then the contents of this temp table's records can be displayed in the row order in which the records were stored?

select CASE WHEN (GROUPING(MthTitle) = 1)  THEN 'TOT:'
            WHEN (GROUPING(OfficeNumber) = 1) THEN 'SUB:'
            ELSE MthTitle
       END AS MthTitle
     , DollarTitle, OfficeNumber, CustomerNumber, FirstName, LastName, StreetAddr1, City,
ResStateCode, DateLost
     , sum(AcctValue) AcctValue
from tblSummMod td
group by MthTitle, DollarTitle, OfficeNumber, CustomerNumber, FirstName, LastName, StreetAddr1, City,
ResStateCode, DateLost with rollup
having (GROUPING(OfficeNumber) = 1 and GROUPING(DollarTitle) = 0)
    or GROUPING(DollarTitle)
       + GROUPING(CustomerNumber)
       + GROUPING(FirstName)
       + GROUPING(LastName)
       + GROUPING(StreetAddr1)
       + GROUPING(City)
       + GROUPING(ResStateCode)
       + GROUPING(DateLost) = 0
      or GROUPING(MthTitle) = 1
order by DollarTitle Desc,
         CASE WHEN (GROUPING(MthTitle) = 1)  THEN char(255)
              ELSE td.DollarTitle
       , CASE WHEN (GROUPING(OfficeNumber) = 1) THEN char(254)
              ELSE td.MthTitle
       , AcctValue desc
Question by:zimmer9
    LVL 26

    Expert Comment

    The only way to guarantee the order of any query is to use an ORDER BY clause. Therefore, if you need a specific order, populate a column with a value that matches the desired order. For example, you might increment a column value by (1) each time you inserts a record. The sequential numbers would allow sequential retrieval.

    Are you creating the table or is it an existing table that you must use?


    Author Comment

    I want to store the RESULT SET of the SQL statement above into a NEW table and export this newly created table to an Excel file. Can you think of way of doing so? Is there a way to perform a SELECT statement on the NEWLY created table records to export the records in the same order as would be the case of executing the SQL Statement shown above.

    Here is my issue. I'm writing a C# program. I want to create a STORED PROCEDURE and then I pass the stored procedure to a routine that executes the STORED PROCEDURE and populates the result set into an Excel file. And the records should be displayed with detail records, followed by Sub total, followed by more detail records, followed by another Sub total and finally a TOTAL record within the exported file to Excel.
    LVL 26

    Accepted Solution

    And again, yes, you can specify an order by using ORDER BY. SQL doesn't supply any other way. Your SQL includes ORDER BY. Whatever consumes the result set will receive the rows according to your ORDER BY. If it writes output in an Excel file format (or will it be .CSV or some other non-Excel format?), the output will be in the order of the result set.

    If it adds sub-total and total records at your desired points, then they'll be in the order of your SQL statement.

    Is your stored proc going to execute your SQL? If it is, then your routine will receive rows in order, process groups in order, output sub-totals in order and output a final total at the end.

    Or is your routine first going to INSERT each result set row into your 'NEW' table, and then after it's finished there, it will start retrieving rows from the 'NEW' table and use those new rows to build the Excel output with sub-totals?


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    729 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

    19 Experts available now in Live!

    Get 1:1 Help Now