Pre-copy script to insert Blob file data to existing SQL table, using synapse analytics pipeline

Question

I have got a file in Blob storage and I want to add data from it to my dataset (using synapse analytics pipeline 'Copy data' activity). Usually I would use a pre-copy script TRUNCATE TABLE a.Table, but this will rewrite the table.

I need to leave the old data and append data in the table instead of clearing and repopulating it. I have tried to use the pre copy script INSERT INTO a.Table and INSERT INTO a.Table SELECT * FROM folder/x.csv as source is already stated in copy data activity, but both ways throw me an error.

This is the error:

{
    "errorCode": "2200",
    "message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Parse error at line: 2, column: 32: Incorrect syntax near 'csv'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Parse error at line: 2, column: 32: Incorrect syntax near 'csv'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=103010,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=103010,State=1,Message=Parse error at line: 2, column: 32: Incorrect syntax near 'csv'.,},],'",
    "failureType": "UserError",
    "target": "Append",
    "details": []
}

What pre-copy script I need to use to append data in a table with data from the Blob file?

Answer 1

If you want to append data in SQL table using ADF, do not add any pre-copy script. By default, data will be inserted into the table along with the existing data.

• Take the source dataset from blob storage in copy activity.

• Give the sink settings as below image.

• When you run the pipeline with copy activity for the first time, data will be inserted into the empty table.

• When the pipeline is run again for the second time, data will be copied to the sink and existing data in the sink will also remain there.