Upsert to Azure SQL Database Failed because Interim Table Exists

Su Wang 10 Reputation points
2023-01-27T22:07:31.5066667+00:00

I have created a Copy Activity on Azure Data Factory that moves the data from a CSV file hosted on Data Lake to Azure SQL Database.

When I select the write behavior to be INSERT, the pipeline runs fine. But when I changed to UPSERT and provide the proper key column, the pipeline fails with the following error message:

Operation on target Copy CSV into Big Table failed: ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=There is already an object named '##InterimTable_209cbcef-08cd-4b73-9967-e49cad4bfcc2' in the database.,Source=.Net SqlClient Data Provider,SqlErrorNumber=2714,Class=16,ErrorCode=-2146232060,State=6,Errors=[{Class=16,Number=2714,State=6,Message=There is already an object named '##InterimTable_209cbcef-08cd-4b73-9967-e49cad4bfcc2' in the database.,},],'

When I query the Azure SQL Database, I could not find such '##InterimTable_209cbcef-08cd-4b73-9967-e49cad4bfcc2' table.

The file I am trying to read has 50,000 records, each with 1025 integers. The Azure SQL table has 1024 columns of integers already created. I have mapping defined for the CopyActivity so that those columns are mapped to the proper field.

How come the INSERT can success but UPSERT fails?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,540 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee
    2023-01-30T06:01:02.3533333+00:00

    Hi Su Wang ,

    Welcome to Microsoft Q&A platform and thanks for posting your query here.

    I understand that you are facing an error : '##InterimTable_209cbcef-08cd-4b73-9967-e49cad4bfcc2' in the Azure SQL Database, but that table already exists' while performing upsert in the database.

    • This can happen when the pipeline is run multiple times, and the temporary table is not being dropped properly between runs.
    • When you select the write behavior to be INSERT, the pipeline simply adds new rows to the target table without checking for duplicates. However, when you change the write behavior to UPSERT, the pipeline first creates a temporary table to hold the incoming data, then performs a merge operation to insert new rows and update existing rows in the target table based on the specified key column. This is likely why you see the error when trying to perform an UPSERT but not when performing an INSERT.

    You can try following approaches:

    1. Try to add a step in the pre copy script of copy data activity to drop the temporary table , in case it exists from a previous run.

    IF OBJECT_ID(N'##InterimTable_209cbcef-08cd-4b73-9967-e49cad4bfcc2') IS NOT NULL

BEGIN

DROP TABLE ##InterimTable_209cbcef-08cd-4b73-9967-e49cad4bfcc2

END

GO

    2. You can also try to TRUNCATE the base table and then try to perform UPSERT once.

    Checkout this video on how to perform upsert for incremental records

    Hope it helps. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well.