Difference in Data When Perform SELECT or SELECT INTO

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 ++++

Answer 1

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.

Answer 2

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 ++++