Hello werners,

Thank you for your reply.

In your link I found this link, where is this peace of code:

# Load data from an Azure Synapse query.
df = spark.read \
[...]
 
# Apply some transformations to the data, then use the
# Data Source API to write the data back to another table in Azure Synapse.
 
df.write \
  .format("com.databricks.spark.sqldw") \
  .option("url", "jdbc:sqlserver://<the-rest-of-the-connection-string>") \
  .option("forwardSparkAzureStorageCredentials", "true") \
  .option("dbTable", "<your-table-name>") \
  .option("tempDir", "wasbs://<your-container-name>@<your-storage-account-name>.blob.core.windows.net/<your-directory-name>") \
  .save()

However, it is not a good solution for me.

First of all, what if I want to update one row in the table that has a few milions records?

Do I need to download the entire table into dataframe, update one row and resend to Azure SQL? It looks really ineffective.

Moreover, what if I want to make update on table which has an auto-indexing column? I cannot download the table to a dataframe, update with my own id value, and resend to Azure SQL.

That is why I am using PyOdbc now, because I can make real sql insert / update.

Is there any elegant solution at Databricks?

Best regards,

Łukasz