Insert into..Select with order by

Question

I'm having a problem with inserting data into a table in specific order from another table.I want ordered data by column Niveau

My query is like :

IF OBJECT_ID('tempdb..#DISPLAYVALUEALL') IS NOT NULL
        DROP TABLE #DISPLAYVALUEALL

SELECT  
    Ecritures.RecIdLine    AS RecIdLine, 
    Ecritures.DISPLAYVALUEComb AS DISPLAYVALUEComb,
    LevelTable.DIMENSIONATTRIBUTE   AS DimAttribute,
    LevelTable.LEVEL_          AS Niveau,
    DimAttr.Name               AS Dim_Label
INTO 
    #DISPLAYVALUEALL
FROM 
    #BudgetTransTmp     As Ecritures            
INNER JOIN 
    #InterfacesParmTmp  AS Tmp1 ON Tmp1.DATAAREAID = Ecritures.DATAAREAID   
INNER JOIN 
    MicrosoftDynamicsAx.dbo.DIMENSIONHIERARCHYLEVEL As LevelTable ON LevelTable.DIMENSIONHIERARCHY = Ecritures.ACCOUNTSTRUCTURE
                                   AND LevelTable.PARTITION = @Partition
                                   AND LevelTable.DIMENSIONATTRIBUTE IN (Tmp1.TaskCode, Tmp1.Activity, Tmp1.BudgetNature, Tmp1.CentreCout)
INNER JOIN 
    MicrosoftDynamicsAx.dbo.DIMENSIONATTRIBUTE As DimAttr ON DimAttr.RECID = LevelTable.DIMENSIONATTRIBUTE
                              AND DimAttr.PARTITION = @Partition
ORDER BY  
    RecIdLine,DISPLAYVALUEComb , Niveau ASC, DimAttribute DESC

    --SELECT *  from #DISPLAYVALUEALL

This doesn't work. Is it illegal to use an order by in a select into statement?

This is my result

ReciID      DisplayValue        Dimension   Niveau
5637157342  601200-001-026--    22565421189 5
5637157342  601200-001-026--    22565421195 2
5637157342  601200-001-026--    22565421196 3
5637157342  601200-001-026--    22565421197 4

5637157326  601200-001-027--    22565421189 5
5637157326  601200-001-027--    22565421195 2
5637157326  601200-001-027--    22565421196 3
5637157326  601200-001-027--    22565421197 4

Thanks!

Update : my solution is adding this line :

Row_number() over(ORDER BY Ecritures.RecIdLine, Ecritures.DISPLAYVALUEComb, LevelTable.LEVEL_ ASC, LevelTable.DIMENSIONATTRIBUTE DESC) as RowNumber,

And my script becomes :

IF OBJECT_ID('tempdb..#DISPLAYVALUEALL') IS NOT NULL
            DROP TABLE #DISPLAYVALUEALL
    SELECT  Row_number() over(ORDER BY Ecritures.RecIdLine  ,Ecritures.DISPLAYVALUEComb , LevelTable.LEVEL_  asc, LevelTable.DIMENSIONATTRIBUTE  desc) as RowNumber,
                 Ecritures.RecIdLine       AS RecIdLine, 
                Ecritures.DISPLAYVALUEComb AS DISPLAYVALUEComb,
                LevelTable.DIMENSIONATTRIBUTE   AS DimAttribute,
                LevelTable.LEVEL_          AS Niveau,
                DimAttr.Name               AS Dim_Label

        INTO #DISPLAYVALUEALL
        FROM #BudgetTransTmp        As Ecritures            

            INNER JOIN #InterfacesParmTmp   AS Tmp1
                ON Tmp1.DATAAREAID          = Ecritures.DATAAREAID  

            INNER JOIN MicrosoftDynamicsAx.dbo.DIMENSIONHIERARCHYLEVEL      As LevelTable
                ON LevelTable.DIMENSIONHIERARCHY    = Ecritures.ACCOUNTSTRUCTURE
                AND LevelTable.PARTITION            = @Partition
                AND LevelTable.DIMENSIONATTRIBUTE   IN (Tmp1.TaskCode,Tmp1.Activity, Tmp1.BudgetNature, Tmp1.CentreCout)

            INNER JOIN MicrosoftDynamicsAx.dbo.DIMENSIONATTRIBUTE   As DimAttr
                ON DimAttr.RECID                    = LevelTable.DIMENSIONATTRIBUTE
                AND DimAttr.PARTITION               = @Partition 
               ORDER BY RowNumber

Now the data is ordered in table I check by :

SELECT * from #DISPLAYVALUEALL

Rows aren't logically ordered in a table. If you want your last `SELECT` to display the data in a particular order, *that's* where you should add the `ORDER BY` clause. — Matt Gibson, Jan 12 '16 at 14:29
Because i need this ordrer in second table. i found somthing check my update — stoner, Jan 12 '16 at 14:35
Like I say, data isn't ordered in tables. (If you have a clustered index, then data can by physically stored in that order, which may help with efficiency, but there's still no guarantee it'll *come out* in that order with a `SELECT` statement.) If you do a `SELECT` without an `ORDER BY` then the server can give you the data in any order it sees fit, and that can change from one minute to the next. What's the problem with you just putting the `ORDER BY` on the `SELECT` that you want the ordered results from? — Matt Gibson, Jan 12 '16 at 14:48
Your "solution" may break at any time. As I've said, if you don't have an `ORDER BY` on a `SELECT` statement, the server may return the rows in any order. And just because it's coming out in the order you want today, doesn't mean it'll come out in that order tomorrow. — Matt Gibson, Jan 12 '16 at 15:03
Thank you for explanation .Mybe I have not explained clearly ,firstly when i use ontly the clause `order by` my table isn't ordered by default , but now with my updating the table is ordered. Because i need this table was by default ordered.Check my update — stoner, Jan 12 '16 at 15:10
I was add `ORDER BY RowNumber` as Frisbee say to me. what do you think now? — stoner, Jan 12 '16 at 15:14

paparazzo · Answer 1 · 2016-01-12T14:43:08.603

0

There is not purpose to ordering the insert

In absence of an order by a select has no guaranteed order

You need to move the ORDER BY

SELECT * from #DISPLAYVALUEALL
ORDER BY RecIdLine, DISPLAYVALUEComb , Niveau asc, DimAttribute desc

or you can just use

SELECT * from #DISPLAYVALUEALL
 ORDER BY RowNumber

edited Jan 12 '16 at 14:43

answered Jan 12 '16 at 14:36

paparazzo

44,497
23
105
176

i'm using that for the moment : `Row_number() over(ORDER BY Ecritures.RecIdLine ,Ecritures.DISPLAYVALUEComb , LevelTable.LEVEL_ asc, LevelTable.DIMENSIONATTRIBUTE desc) as RowNumber,` i think the order is ok – stoner Jan 12 '16 at 14:41
1

I can see what you are using. For the second time "In absence of an order by a select has no guaranteed order". – paparazzo Jan 12 '16 at 14:42

score -1 · Answer 2 · answered Jan 12 '16 at 14:28

-1

Try to change your select query as following and execute again:

SELECT T.*
  INTO #DISPLAYVALUEALL
  FROM (SELECT  
         Ecritures.RecIdLine       AS RecIdLine, 
        Ecritures.DISPLAYVALUEComb AS DISPLAYVALUEComb,
        LevelTable.DIMENSIONATTRIBUTE   AS DimAttribute,
        LevelTable.LEVEL_          AS Niveau,
        DimAttr.Name               AS Dim_Label

FROM #BudgetTransTmp        As Ecritures            

    INNER JOIN #InterfacesParmTmp   AS Tmp1
        ON Tmp1.DATAAREAID          = Ecritures.DATAAREAID  

    INNER JOIN MicrosoftDynamicsAx.dbo.DIMENSIONHIERARCHYLEVEL      As LevelTable
        ON LevelTable.DIMENSIONHIERARCHY    = Ecritures.ACCOUNTSTRUCTURE
        AND LevelTable.PARTITION            = @Partition
        AND LevelTable.DIMENSIONATTRIBUTE   IN (Tmp1.TaskCode,Tmp1.Activity, Tmp1.BudgetNature, Tmp1.CentreCout)

    INNER JOIN MicrosoftDynamicsAx.dbo.DIMENSIONATTRIBUTE   As DimAttr
        ON DimAttr.RECID                    = LevelTable.DIMENSIONATTRIBUTE
        AND DimAttr.PARTITION               = @Partition                        

 ORDER BY RecIdLine,DISPLAYVALUEComb , Niveau asc, DimAttribute desc) T

answered Jan 12 '16 at 14:28

Khazratbek

1,656
2
10
17

Thank you ,I think I found something. i updated my question – stoner Jan 12 '16 at 14:36
@stoner try to use the same principle: make a subquery – Khazratbek Jan 12 '16 at 14:46
I test your solution but it give me this error : `The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.` – stoner Jan 12 '16 at 15:17
@stoner first of all, try to execute your sql query without INTO command, just selection – Khazratbek Jan 12 '16 at 15:25
@stoner does it give you result? if no, it means that select query is not correct – Khazratbek Jan 12 '16 at 15:25
My sql is Ok like i put in my answer on the my solution i.Just i want to know if we can do it otherwise. – stoner Jan 12 '16 at 15:33
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/100491/discussion-between-khazratbek-and-stoner). – Khazratbek Jan 12 '16 at 16:00

Insert into..Select with order by

2 Answers2