question

SoonJooGenting-3682 avatar image
0 Votes"
SoonJooGenting-3682 asked ShaikMaheer-MSFT commented

Create temp table in Synapse Serverless SQL

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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SoonJooGenting-3682,

Just checking in to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hi @SoonJooGenting-3682 ,

Following up to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @SoonJooGenting-3682,

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


image.png (367.2 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Maheer,

I have tried to pass the same value to the hash functions, but I'm still getting the following error. For your information I'm trying to create the table in the serverless SQL pool. Is there a limitation which prevent me from creating the temp table? As I read from the docs, temp table should be able to be created in serverless SQL pool as well.

138989-screenshot64.jpg





0 Votes 0 ·
screenshot64.jpg (195.5 KiB)

Hi @SoonJooGenting-3682,

I got you now. If we would like to create temp tables in Server less SQL pool, you should connect to your server less SQL Pool using client tool such as SSMS. In Browser, it will give message like temp table created but when you perform select * from on that temp table, it says invalid object.

Please Note, Temp tables in Server less SQL Pool will not allow you to pass distributions. So you need to remove that WITH keyword block completely.

You can refer below link to know more about details about temp. tables in Azure Synapse.
https://www.youtube.com/results?search_query=temp+tables+in+Synapse
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-temporary

Please check below screenshots, Where I show temp. tables creation in server less SQL pool using browser and also using SSMS
139312-image.png

To connect Server less SQL pool using SSMS you should use your server less sql pool end point as server name in SSMS.
139322-image.png

Hope this will help. Please consider hitting Accept Answer. Accepted answers helps community as well.

0 Votes 0 ·
image.png (148.5 KiB)
image.png (120.5 KiB)

Hi Maheer,

Thanks for your clarification, I am able to create the temp table as you demonstrate above. However, I do have problem selecting the data from external table and insert into the temp table. Also the temp table is "disappear" after the queries, is this intended as temp table only persists in the session?

0 Votes 0 ·

Hi @SoonJooGenting-3682 ,

Yes temp tables will be available only with in the session. Please feel free to open new thread to discuss more.

Also, please consider marking it as accepted answer. Accepted answers helps community as well.

0 Votes 0 ·