ADF Copy Data Activity with upsert Scenario - inserts data when the key columns are NULL from source instead of update

Arul P 6 Reputation points
2022-04-05T21:52:32.277+00:00

Hello Experts, I am trying to load data form a SQLDW table to another table. Need to perform upsert using copy activity. I have 5 columns in SINK defined as key columns. among them 2 columns might come with NULL values. In this case, I see a new record inserted rather than update.

Any alternates here to overcome this NULL issue?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,120 questions
{count} vote

2 answers

Sort by: Most helpful
  1. Saurabh Sharma 23,791 Reputation points Microsoft Employee
    2022-04-06T23:08:18.85+00:00

    Hi @Arul P ,

    Thanks for using Microsoft Q&A!!
    If I understand you correctly, you are trying to use Copy activity with Upsert, however, the activity is inserting a new record if NULL comes against few of the key column values.
    I have tested this and I could see the same behavior and it looks like NULL is being treated as a new record.
    190706-image.png

    I am checking internally with the products team on this and get back to as soon as I have any updates.
    I have not tested this but you can try writing using custom logic and apply additional processing before inserting the source data into the destination table by invoking a stored procedure to do the upsert task instead of using the built-in Upsert. Please refer to the Invoke a stored procedure from a SQL sink for details.

    Please let me know if you have any questions.

    Thanks
    Saurabh

    1 person found this answer helpful.

  2. Dirk Sachse 106 Reputation points
    2023-03-21T08:51:31.38+00:00

    I just ran into the exact same problem and spent a considerable amount of time to research this. It should be documented somewhere, how the upsert function works and that key columns can't be NULL. Also if you have a series of records with the same key combination within one batch of data, upsert won't work either.

    My solution was to replace NULL with something else, but I m not that happy with it.

    0 comments No comments