Question 104124 in Azure Question posted in
The official documentation to get you started can be found here.

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search