Not able to create table in Azure Synapse Analytics

Question

I am new in Azure Synapse Analytics. I have created database in workspace but when I am trying to create a new table it is showing below error.

Failed to execute query. Error: CREATE TABLE <table_name> is not supported.

Attaching screenshot of error and query.

Mural

Answer 1

it seems that you are connected to the built-in SQL serverless pool.

The Serverless SQL pool has no local storage, only metadata objects are stored in databases. Therefore, T-SQL related to the following concepts isn't supported:

Tables
Triggers
Materialized views
DDL statements other than ones related to views and security
DML statements

You can use CETAS - CREATE EXTERNAL TABLE AS SELECT or instead you can create a provisioned SQL Pool.

Kalat

This is where you can get more info about this: learn.microsoft.com/en-us/azure/synapse-analytics/sql/… – Witwatersrand

Answer 2

Its a Bug in the 'Flat File' import wizard of Azure Data Studio / databricks / Synapse Pipes.
In a Serverless Pool; "VIEW" is the DBO container.

You can try to have Synapse generate the code for you; (using plethora of options).
then Edit said code using the below:

USE YourDataBase CREATE VIEW

USE YourDataBase IF NOT EXIST CREATE EXTERNAL TABLE as ()

Likewise:
DROP VIEW and DROP EXTERNAL TABLE

importwizardfail

Tinsley