Create temp table in Synapse Serverless SQL

Question

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.

Answer 1

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.

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

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

• Please don't forget to click on or upvote 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