title: How to Insert Data into table with select query in Databricks using spark temp table
body: <p>I would like to insert the results of a Spark table into a new SQL Synapse table using SQL within Azure Data Bricks.</p>
  <p>I have tried the following explanation [https://docs.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.</p>
  <p>The Synapse table must be created as a result of a <strong>SELECT</strong> statement. The source should be a Spark / Data Bricks temporary view or Parquet source.</p>
  <p>e.g. <strong>Temp Table</strong></p>
  <pre><code>    # Load Taxi Location Data from Azure Synapse Analytics
          
          jdbcUrl = &quot;jdbc:sqlserver://synapsesqldbexample.database.windows.net:number;
  database=SynapseDW&quot; #Replace &quot;suffix&quot; with your own  
          connectionProperties = {
            &quot;user&quot; : &quot;usernmae1&quot;,
            &quot;password&quot; : &quot;password2&quot;,
            &quot;driver&quot; : &quot;com.microsoft.sqlserver.jdbc.SQLServerDriver&quot;
          }
          
          pushdown_query = '(select * from NYC.TaxiLocationLookup) as t'
          dfLookupLocation = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)
          
          dfLookupLocation.createOrReplaceTempView('NYCTaxiLocation')
          
          display(dfLookupLocation)
  </code></pre>
  <p>e.g. <strong>Source Synapse DW</strong></p>
  <p><em>Server:</em> synapsesqldbexample.database.windows.net</p>
  <p><em>Database:</em>[SynapseDW]</p>
  <p><em>Schema:</em> [NYC]</p>
  <p><em>Table:</em> [TaxiLocationLookup]</p>
  <p><strong>Sink / Destination Table (not yet in existence):</strong></p>
  <p><em>Server:</em> synapsesqldbexample.database.windows.net</p>
  <p><em>Database:</em>[SynapseDW]</p>
  <p><em>Schema:</em> [NYC]</p>
  <p><em>New Table:</em> [TEST_NYCTaxiData]</p>
  <p><strong>SQL Statement I tried:</strong></p>
  <pre><code>%sql
  CREATE TABLE if not exists TEST_NYCTaxiLocation 
  select *
  from NYCTaxiLocation
  limit 100
  </code></pre>