Order by Clause does not work with Select * into #temp statement in sql server 2008 express.
My script is like that.
SELECT BCODE+REPLICATE(' ',20-LEN(BCODE))'BCODE',DES+REPLICATE(' ',20-LEN(DES))'DESCRIPTION',RETAILS+REPLICATE(' ',5-LEN(RETAILS))'RETAIL'
,DISCOUNT+REPLICATE(' ',5-LEN(DISCOUNT))'DISCOUNT' Into TmpItems FROM (SELECT CAST(ART_REFNUMMER AS VARCHAR) 'BCODE'
,RTrim (SUBSTRING(ABT_TEXT, 1, 20)) 'DES',CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) 'RETAILS',CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) 'DISCOUNT'
From V_ARTIKEL, V_ABTEIL, V_ART_KOPF
Where ART_ABTEILUNG = ABT_NUMMER AND ART_WARENGR=AGR_WARENGR AND ART_ABTEILUNG=AGR_ABTEILUNG AND ART_TYPE=AGR_TYPE
AND ART_GRPNUMMER=AGR_GRPNUMMER AND ART_LIEFERANT=AGR_LIEFERANT ) T1
UNION
SELECT BCODE+REPLICATE(' ',20-LEN(BCODE))'BCODE',DES+REPLICATE(' ',20-LEN(DES))'DESCRIPTION',RETAILS+REPLICATE(' ',5-LEN(RETAILS))'RETAIL'
,DISCOUNT+REPLICATE(' ',5-LEN(DISCOUNT))'DISCOUNT'
FROM (SELECT CAST(AEA_EANCODE AS VARCHAR) 'BCODE',RTrim (SUBSTRING(ABT_TEXT, 1, 20)) 'DES',CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) 'RETAILS'
,CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) 'DISCOUNT' From V_ARTIKEL, V_ABTEIL, V_ART_KOPF, V_ART_EANS
Where ART_ABTEILUNG = ABT_NUMMER AND ART_WARENGR=AGR_WARENGR AND ART_ABTEILUNG=AGR_ABTEILUNG AND ART_TYPE=AGR_TYPE AND
ART_GRPNUMMER=AGR_GRPNUMMER AND ART_LIEFERANT=AGR_LIEFERANT) T1 Order By BCODE
SELECT [BCODE], [DESCRIPTION], [RETAIL], [DISCOUNT] from [dbo].[TmpItems]
For anyone that finds formatted code easier to read... (I don't time this evening to break it all down, but there are is a whole host of "bad TSQL habits" on display here...
SELECT
BCODE = T1.BCODE + REPLICATE(' ', 20 - LEN(T1.BCODE)),
[DESCRIPTION] = T1.DES + REPLICATE(' ', 20 - LEN(T1.[DES])),
RETAIL = T1.RETAILS + REPLICATE(' ', 5 - LEN(T1.RETAILS)),
DISCOUNT = T1.DISCOUNT + REPLICATE(' ', 5 - LEN(T1.DISCOUNT))
INTO TmpItems
FROM (
SELECT
BCODE = CAST(ART_REFNUMMER AS VARCHAR),
DES = RTRIM(SUBSTRING(ABT_TEXT, 1, 20)),
RETAILS = CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR),
DISCOUNT = CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR)
FROM
V_ARTIKEL,
V_ABTEIL,
V_ART_KOPF
WHERE
ART_ABTEILUNG = ABT_NUMMER
AND ART_WARENGR = AGR_WARENGR
AND ART_ABTEILUNG = AGR_ABTEILUNG
AND ART_TYPE = AGR_TYPE
AND ART_GRPNUMMER = AGR_GRPNUMMER
AND ART_LIEFERANT = AGR_LIEFERANT
) T1
UNION
SELECT
BCODE = T1.BCODE + REPLICATE(' ', 20 - LEN(T1.BCODE)),
[DESCRIPTION] = T1.DES + REPLICATE(' ', 20 - LEN(T1.[DES])),
RETAIL = T1.RETAILS + REPLICATE(' ', 5 - LEN(T1.RETAILS)),
DISCOUNT = T1.DISCOUNT + REPLICATE(' ', 5 - LEN(T1.DISCOUNT))
FROM (
SELECT
BCODE = CAST(AEA_EANCODE AS VARCHAR),
[DES] = RTRIM(SUBSTRING(ABT_TEXT, 1, 20)),
RETAILS = CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR),
DISCOUNT = CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR)
FROM
V_ARTIKEL,
V_ABTEIL,
V_ART_KOPF,
V_ART_EANS
WHERE
ART_ABTEILUNG = ABT_NUMMER
AND ART_WARENGR = AGR_WARENGR
AND ART_ABTEILUNG = AGR_ABTEILUNG
AND ART_TYPE = AGR_TYPE
AND ART_GRPNUMMER = AGR_GRPNUMMER
AND ART_LIEFERANT = AGR_LIEFERANT
) T1
ORDER BY
BCODE;
SELECT
ti.BCODE,
ti.[DESCRIPTION],
ti.RETAIL,
ti.DISCOUNT
FROM
dbo.TmpItems ti;
It's not a guarantee, but adding a clustered index to the table would (almost) always return the data in order, and, more importantly, it would always avoid a sort every time the query is run.
Of course you'll have to modify the code to add the clus index while keeping the table creation dynamic:
create the table, using into, but with no rows
add the clustered index
load the table using insert ... with (tablock) to get minimal logging if possible
SELECT TOP (0) BCODE+REPLICATE(’ ‘,20-LEN(BCODE))‘BCODE’,DES+REPLICATE(’ ‘,20-LEN(DES))‘DESCRIPTION’,RETAILS+REPLICATE(’ ‘,5-LEN(RETAILS))‘RETAIL’
,DISCOUNT+REPLICATE(’ ',5-LEN(DISCOUNT))‘DISCOUNT’
Into TmpItems
FROM (SELECT CAST(ART_REFNUMMER AS VARCHAR) ‘BCODE’
,RTrim (SUBSTRING(ABT_TEXT, 1, 20)) ‘DES’,CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) ‘RETAILS’,CAST(CAST(ART_VKPREIS AS INT) AS VARCHAR) ‘DISCOUNT’
From V_ARTIKEL, V_ABTEIL, V_ART_KOPF
Where ART_ABTEILUNG = ABT_NUMMER AND ART_WARENGR=AGR_WARENGR AND ART_ABTEILUNG=AGR_ABTEILUNG AND ART_TYPE=AGR_TYPE
AND ART_GRPNUMMER=AGR_GRPNUMMER AND ART_LIEFERANT=AGR_LIEFERANT ) T1
--don't need the UNION here
CREATE CLUSTERED INDEX TmpItems__CL ON dbo.TmpItems ( BCODE ) WITH ( FILLFACTOR = 100 );
INSERT INTO dbo.TmpItems
SELECT TOP (0) BCODE+REPLICATE(’ ‘,20-LEN(BCODE))‘BCODE’,DES+REPLICATE(’ ‘,20-LEN(DES))‘DESCRIPTION’,RETAILS+REPLICATE(’ ‘,5-LEN(RETAILS))‘RETAIL’
,DISCOUNT+REPLICATE(’ ',5-LEN(DISCOUNT))‘DISCOUNT’
FROM (SELECT CAST(ART_REFNUMMER AS VARCHAR) ‘BCODE’
...rest same as before, including the union...