CODEWITHSUNDEEP
azuret-sqlserverlessazure-synapse
lyubol
lyubol

Reputation: 129

Create external table from CTE in Azure Synapse Serverless SQL Pool

In Azure Synapse Serverless SQL Pool, I have a chain on CTEs. I want to store the result inside an external table in Azure Data Lake Storage. My query looks like that:

WITH CTE1 AS (
  <query logic>
), CTE2 AS (
  <query logic>
), CTE3 AS (
  <query logic>
)
SELECT * FROM CTE3

I tried the following:

WITH CTE1 AS (
  <query logic>
), CTE2 AS (
  <query logic>
), CTE3 AS (
  <query logic>
)
CREATE EXTERNAL TABLE [tmp].[Table] WITH (
    LOCATION = 'TEMP/',
    DATA_SOURCE = [data_lake_external_data_source],
    FILE_FORMAT = [parquet_external_file_format]
) AS
SELECT * FROM CTE3

which results in this error: 'Incorrect syntax near the keyword 'CREATE'.'

Also, I have tried creating an empty external table and inserting data into it using a SELECT statement after the CTE but, it looks like you can't insert data into an external table.

What would be the right way to store the output of the CTE? It could be temp table, external table, etc.

Upvotes: 0

Views: 1145

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8392

The error you are facing because of A CTE (Common Table Expression) is a one-time result set that only exists for the duration of the query. We can't use create statement in under the select.

To Create external table from CTE in Azure Synapse Serverless SQL Pool you can follow below code:

CREATE  EXTERNAL  TABLE [dbo].[Table1] WITH (
LOCATION = 'File path',
DATA_SOURCE = Data_source_name,
FILE_FORMAT = File_Formaat
) AS
WITH CTE1 AS (
  <query logic>
), CTE2 AS (
  <query logic>
), CTE3 AS (
  <query logic>
)
SELECT * FROM CTE3

My Execution and Output:

enter image description here

Upvotes: 0

Related Questions