Azure Synapse Analytics – Create Temp Table from SELECT with ORDER BY

azure-synapse-analyticst-sqltemporary-tables

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 [table], 
       columns.column_id as [ordinal],
       columns.name as [column],
       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

Best Answer

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 [table], 
    C.[column_id] as [ordinal],
    C.[name] as [column],
    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.

Related Question