SELECT from multiple table and insert in a sorted order in sql

Question

Looks like the data is not inserting in a order from select. Logic looks fine for my from my knowledge. Now sure where i am wrong ? Can you please help.

DECLARE @ReportDate DATE;
    SET @ReportDate = '20130930';
    TRUNCATE TABLE #tmpIntTabel;
    INSERT INTO #tmpCareTabel (
        [SK_ServiceProviderID]
        ,[ReportYear]
        ,[ReportMonth]
        ,[CommissionerCode]
        ,[ServiceProviderName]
        )
        select * from       
        (SELECT DISTINCT tc.SK_ServiceProviderID 
            ,t.Years
            ,t.Months
            ,tc.CommissionerCode
            ,tc.ServiceProviderName
            FROM #TargetClientGroup tc
            INNER JOIN 
            (SELECT
                SK_ServiceProviderID
                ,datepart(yy, dateadd(m, datediff(m, 0, ReportDate), 0)) as Years
                ,datename(mm, dateadd(m, datediff(m, 0,ReportDate), 0)) as Months
                FROM dbo.Component
                WHERE ReportDate <= @ReportDate
                GROUP BY    SK_ServiceProviderID
                            ,ReportDate) t
            on tc.SK_ServiceProviderID = t.SK_ServiceProviderID) a 
    ORDER BY Years
    ,Months
    ,CommissionerCode

After insert when i select the table. i get the unordered data.

When you do a `SELECT`, and you want to have a specific order, you **always MUST** supply an `ORDER BY` clause. There is no **implicit** (or automatic) order in any relational database — marc_s, Oct 04 '13 at 11:10
Yes i agree with this and hence supplied. But i am afraid it dosn't work properly — user2637506, Oct 04 '13 at 11:14
Well it seems that you're trying to insert data in a specific order, assuming that it will be returned in that same order again. This is **NOT** the case! No matter how you insert the data - the table never has any ordering per se. You **only** get an ordered result set if you **explicitly** use an `ORDER BY` on your `SELECT` statement when retrieving the data. — marc_s, Oct 04 '13 at 11:16
Thank you for the nice explaination. And i totally agree with the statement. This is a stored procedure where the table is created and insertes the daya by a select of multiple temp table. So when i execute the procedure. i want the data to be displayed in a default ordered manner — user2637506, Oct 04 '13 at 11:22
Just to make one more thing clear, because you seem to have missed it from @marc_s's first comment, there is no such thing as a "default (or implicit, or automatic) row order" in SQL. Or, if you like, this default order is *not guaranteed*, meaning that, when you are retrieving data without specifying an ORDER BY, the server is free to return them in any order, and that behaviour is per standard. The order returned *may or may not happen* to be the order in which the order were stored. You should just never rely on that. — Andriy M, Oct 07 '13 at 10:58
If you want to *remember* a particular order without having to specify the same criteria later, one way is to use a dedicated column and populate it at the time of insertion. When retrieving data, you would use just that column to sort the rows. — Andriy M, Oct 07 '13 at 10:59
possible duplicate of [Preserving ORDER BY in SELECT INTO](http://stackoverflow.com/questions/14424929/preserving-order-by-in-select-into) — bummi, May 12 '14 at 08:50

score 0 · Answer 1 · answered Oct 04 '13 at 11:10

0

Try this

ORDER BY t.Years ,t.Months ,tc.CommissionerCode

answered Oct 04 '13 at 11:10

cameronjonesweb

2,435
3
25
37

Sorry this did not work.. I get a haphazard list. I want the insert to be ordered by col1 then col2 then col3 – user2637506 Oct 04 '13 at 11:15
2012 APRIL 3486 hackmanshop 2012 APRIL 3476 backmanshop 2012 may 3466 pmanshop 2012 may 3445 henackmanshop 2013 may 34234 gapmanshop 2013 may 3434 rankmanshop 2013 august 3456 dinkmanshop 2013 september 3456 hackmanshop 2013 september 3419 supermanshop – user2637506 Oct 04 '13 at 11:19

SELECT from multiple table and insert in a sorted order in sql

1 Answers1