When I do the select it only returns the query as I want.
Select Distinct NombreCliente,OELMDT,OEQLVA,CodigoCliente,TLLINO,TLTX60
From #Templino,#Tempcliente
Where (CodigoCliente=CONCAT(RTRIM(#Templino.OEQLVA),'0000'))OR(CodigoCliente = CONCAT(RTRIM(#Templino.OEQLVA),'ZZZZ'))
Order by CodigoCliente, TLLINO asc;
The problem is that when I try to save these results in a temporary table, the order is passed, I don't know where...
create table #Tempclientelino
(
NombreCliente varchar(255),
OELMDT varchar(255),
OEQLVA varchar(255),
CodigoCliente varchar(255),
TLLINO varchar(255),
TLTX60 varchar(255)
)
Insert Into #Tempclientelino
Select Distinct NombreCliente,OELMDT,OEQLVA,CodigoCliente,TLLINO,TLTX60
From #Templino,#Tempcliente
Where (CodigoCliente=CONCAT(RTRIM(#Templino.OEQLVA),'0000'))OR(CodigoCliente = CONCAT(RTRIM(#Templino.OEQLVA),'ZZZZ'))
Order by CodigoCliente, TLLINO asc;
SQL server tables, of any relational database actually, do not have an associated ordering and it is necessary to specify the order by clause to obtain a certain ordering.
SQL Server does not guarantee any order unless we use the clause
ORDER BY
, it does not matter if the order is given when inserting, nor, if we also physically get the records to be inserted with said order, we have no guarantee that said order will be respected at the time of do aSELECT
unless we use aORDER BY
. Assuming that the insertion order is the "natural order" usedSELECT
by a temporary table may be due to a behavior in some of the previous versions, I'm not sure which one, but I do remember serious problems like the one you mention. The solution we found since many times in the temporary ones we do not save the fields by which we want to order: always use an Order fieldINT IDENTITY
, insert with the desired order and then do aORDER BY Orden