Create temp table in Synapse Serverless SQL

SoonJoo@Genting 241 Reputation points
2021-10-08T07:36:21.077+00:00

Hi,

I can create external table with parquet files generated from dataflow. But when I try to create a temp table it returned errors.

I use the following example from the help documentation:

CREATE TABLE #stats_ddl
(
[schema_name] NVARCHAR(128) NOT NULL
, [table_name] NVARCHAR(128) NOT NULL
, [stats_name] NVARCHAR(128) NOT NULL
, [stats_is_filtered] BIT NOT NULL
, [seq_nmbr] BIGINT NOT NULL
, [two_part_name] NVARCHAR(260) NOT NULL
, [three_part_name] NVARCHAR(400) NOT NULL
)
WITH
(
DISTRIBUTION = HASH(0)
, HEAP
)

Any help will be highly appreciated.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,688 questions
{count} votes

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee
    2021-10-08T16:31:25.027+00:00

    Hi @SoonJoo@Genting ,

    Thank you for posting query in Microsoft Q&A Platform.

    HASH() function will expect to pass any column name from your table or temp table. Since you are not passing your temp table column name(any) in to HASH() function, we are ending up with failure.

    Please check below screenshot where I am able to create temp table in Dedicated SQL Pool without any errors when I pass column name in to HASH() function.

    138888-image.png

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators