CODEWITHSUNDEEP
azureazure-synapse
Patterson
Patterson

Reputation: 2757

Azure Synapse Serverless SQL Pool Error: Incorrect syntax near 'DISTRIBUTION'

The following code on Azure Synapse Serverless SQL Pool gives the following error:

Incorrect syntax near 'DISTRIBUTION'.

SELECT CM.EntityName,
    --Before the first column of each table, construct a DROP TABLE statement if already exist
    CASE WHEN CM.OrdinalPosition = 1
        THEN 
        'DROP EXTERNAL TABLE MyTable' + '.' + 
        QUOTENAME(@EnrichedViewSchema) + '.' + CM.EntityName + '
        CREATE TABLE MyTable' + '.' +
        QUOTENAME(@EnrichedViewSchema) + '.' + CM.EntityName + '
        WITH
        (
        DISTRIBUTION = ROUND_ROBIN
        );
        AS
        SELECT DISTINCT '
        ELSE '  ,'
        END

Can someone look at the code and let me know where I might going wrong?

Upvotes: 1

Views: 1989

Answers (2)

Aswin
Aswin

Reputation: 7126

Azure Synapse SQL Server Pool Error: Incorrect syntax near 'DISTRIBUTION'

CREATE TABLE MyTable' + '.' +
        QUOTENAME(@EnrichedViewSchema) + '.' + 
        CM.EntityName + '
        WITH
        (
        DISTRIBUTION = ROUND_ROBIN
        )
  • Serverless SQL pool is used to query over the data lake, and we cannot create tables in it. We can create external tables and temporary tables only in serverless SQL pool.
  • Also, Distribution is applicable only for dedicated SQL pool tables. Therefore, above SQL script is not possible.

enter image description here Reference: screenshot from Microsoft document Design tables using Synapse SQL - Azure Synapse Analytics | Microsoft Learn

Upvotes: 3

Onur Omer
Onur Omer

Reputation: 526

there is an additional semicolon before AS in your script.

Wrong: CREATE TABLE XXX WITH(DISTRIBUTION=ROUND_ROBIN); AS SELECT

Correct: CREATE TABLE XXX WITH(DISTRIBUTION=ROUND_ROBIN) AS SELECT

Upvotes: 0

Related Questions