How to overwrite a table from SQL Server to SQL Dedicated Pool using Copy Data Activity in Azure Synapse Analytics

Question

I am trying to use the Copy Data Activity in Azure Synapse Analytics to copy data from SQL Server to SQL Dedicated Pool. However, I want to ensure that every time I run the Copy Data Activity, the table in SQL Dedicated Pool is overwritten. How can I achieve this?

Answer 1

Hi Aditya Singh,

Thanks for reaching out to Microsoft Q&A.

To overwrite a table in Azure Synapse Dedicated SQL Pool using the Copy Data Activity in Azure Synapse Analytics, follow these steps:

1. Pre-Copy Script (DROP and CREATE):
   • In the Copy Data Activity, use the Pre-copy script option to execute a SQL script before the copy operation begins.
   • In this script, you can include a DROP TABLE IF EXISTS followed by a CREATE TABLE statement to recreate the table. This ensures that the table is overwritten each time the copy runs.
2. Set Sink Write Behavior to Truncate:
   • If you do not want to drop and recreate the table, you can set the Sink Write Behavior to Truncate. This will clear all the data in the target table before inserting the new records.
   • Go to the Sink tab in your Copy Data Activity, and under Settings, set Write Behavior to Truncate. This option is available for SQL Dedicated Pool and will overwrite the existing table content without affecting the schema.
   Steps to Configure in Synapse Pipeline:
   • Source: Configure your SQL Server as the source.
     • Sink: Configure your SQL Dedicated Pool as the sink.
       • Under the Sink settings, in addition to setting the write behavior to Truncate, you may specify necessary mappings if needed.
         • In the Pre-copy script section of the Copy Data activity, you can add the optional script mentioned in step 1 if you need to recreate the table each time.

Note: Dropping and recreating a table can be slower than truncating it, especially for large tables. If your table has indexes or constraints, ensure they are recreated after dropping the table or use the Truncate option instead of Drop/Create.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.