Create an Azure Synapse temp table from a SELECT with ORDER BY

All we need is an easy explanation of the problem, so here it is.

I have the following statement that works when I omit the ORDER BY clause, or if I run it as a select and omit the create table part, but I need both to ensure my generated key is sequenced correctly

Any ideas?

Msg 104381, Level 16, State 1, Line 18
The ORDER BY clause is invalid in views, CREATE TABLE AS SELECT, INSERT SELECT, SELECT INTO, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

CREATE TABLE #demo
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT 
       ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID,
       schemas.name as [schema], 
       tables.name as 
, 
       columns.column_id as [ordinal],
       columns.name as 
,
       types.name as [type]
FROM SYS.COLUMNS
  inner join sys.types
          on  types.system_type_id = columns.system_type_id
  inner join sys.tables
          on  tables.object_id = columns.object_id
  inner join sys.schemas
          on  schemas.schema_id = tables.schema_id
order by schemas.name, 
       tables.name, 
       columns.column_id

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I would imagine this would work for you:

CREATE TABLE #demo
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT 
    ROW_NUMBER() OVER (
        ORDER BY S.[name], T.[name], C.column_id) AS ID,
    S.[name] as [schema], 
    T.[name] as 
, 
    C.[column_id] as [ordinal],
    C.[name] as 
,
    TY.[name] as [type]
FROM sys.columns AS C
JOIN sys.types AS TY
    ON TY.system_type_id = C.system_type_id
JOIN sys.tables AS T
    ON T.[object_id] = C.[object_id]
JOIN sys.schemas AS S
    ON S.[schema_id] = T.[schema_id];

The key difference is moving the ordering to the ROW_NUMBER window function, to determine the order in which numbers are assigned.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , ,

Leave a Reply