I would like to insert the results of a Spark table into a new SQL Synapse table using SQL within Azure Data Bricks.

I have tried the following explanation [https://learn.microsoft.com/en-us/azure/databricks/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-create-table-datasource] but I'm having no luck.

The Synapse table must be created as a result of a SELECT statement. The source should be a Spark / Data Bricks temporary view or Parquet source.

e.g. Temp Table

    # Load Taxi Location Data from Azure Synapse Analytics
        
        jdbcUrl = "jdbc:sqlserver://synapsesqldbexample.database.windows.net:number;
database=SynapseDW" #Replace "suffix" with your own  
        connectionProperties = {
          "user" : "usernmae1",
          "password" : "password2",
          "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        }
        
        pushdown_query = '(select * from NYC.TaxiLocationLookup) as t'
        dfLookupLocation = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)
        
        dfLookupLocation.createOrReplaceTempView('NYCTaxiLocation')
        
        display(dfLookupLocation)

e.g. Source Synapse DW

Server: synapsesqldbexample.database.windows.net

Database:[SynapseDW]

Schema: [NYC]

Table: [TaxiLocationLookup]

Sink / Destination Table (not yet in existence):

Server: synapsesqldbexample.database.windows.net

Database:[SynapseDW]

Schema: [NYC]

New Table: [TEST_NYCTaxiData]

SQL Statement I tried:

%sql
CREATE TABLE if not exists TEST_NYCTaxiLocation 
select *
from NYCTaxiLocation
limit 100