locked
Difference in Data When Perform SELECT or SELECT INTO RRS feed

  • Question

  • Hi Friends,

    I am facing a challenge with returning data with a SELECT and when I insert that SELECT output into a temp table:

     

    /* Create table and insert value */


    CREATE TABLE #T (TableName VARCHAR(50), SchemaName Varchar(20))
    Insert into #T Values ('Employee','EMP')
    Insert into #T Values ('Inventory','INV')
    Insert into #T Values ('SalesOrder','SLE')

    /* SELECT Data and put into a temp table #Schemas */

    IF object_id('tempdb.dbo.#Schemas') IS NOT NULL drop table #Schemas 

    SELECT 
                SchemaName,
    RANK() OVER (ORDER BY CHARINDEX(CAST(TableName AS VARCHAR), 'Inventory,Employee,SalesOrder') ) AS SeqNo
    INTO #Schemas
    FROM
            #T
    WHERE
            TableName IN ('Inventory','Employee','SalesOrder')

            ORDER BY CHARINDEX(CAST(TableName AS VARCHAR), 'Inventory,Employee,SalesOrder')

    /* Do a SELECT on Temp Table - Returning Data in Right Sequence */

    SELECT 
    SchemaName 
    FROM #Schemas
    GROUP BY 
    SchemaName 
    ORDER BY MAX(SeqNo)

    Output 

    INV
    EMP
    SLE

    /* When insert output into a temp table for further use getting wrong ordering */

    IF object_id('tempdb.dbo.#Final') IS NOT NULL drop table #Final  

    SELECT 
    SchemaName 
    INTO #Final
    FROM #Schemas
    GROUP BY 
    SchemaName 
    ORDER BY MAX(SeqNo)

    select * from #Final

    Output

    EMP
    INV
    SLE

    Can you point me to a right direction so that I can get output in the way (order of table).

    REagrds


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Tuesday, December 4, 2018 1:00 AM

Answers

  • A table has no order by definition. This is a basic concept.

    If you need to order your rows from your table, you must use ORDER BY clause for your SELECT statement.

    • Marked as answer by GURSETHI Tuesday, December 4, 2018 1:49 AM
    Tuesday, December 4, 2018 1:14 AM

All replies

  • A table has no order by definition. This is a basic concept.

    If you need to order your rows from your table, you must use ORDER BY clause for your SELECT statement.

    • Marked as answer by GURSETHI Tuesday, December 4, 2018 1:49 AM
    Tuesday, December 4, 2018 1:14 AM
  • I guess, when we too much into something we forget basics and that is what I was doing. Thanks Li for pointing what I was doing wrong :).

    By the way table which I created to store these values, I added a IDENTITY key in that and it resolved my issue.

    IF object_id('tempdb.dbo.#Final') IS NOT NULL drop table #Final  
    Create Table #Final (ID INT IDENTITY (1,1), SchemaName VARCHAR(50))

    INSERT INTO #Final (SchemaName)  
    SELECT 
    SchemaName 
    FROM #Schemas
    GROUP BY 
    SchemaName 
    ORDER BY MAX(SeqNo)

    select SchemaName from #Final

    Output

    INV
    EMP
    SLE

    Regards

    Gurpreet Sethi


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Tuesday, December 4, 2018 1:49 AM