Cannot insert null on identity column when using upsert in Copy data

Question

This is my first post so please bear with me.

• In Azure Data Factory pipeline I'm using a Copy Data action.
• I have my source table(.parquet file) and my target table.
• I want to use Upsert in Sink to copy data to the target table.
• The target table has a field ID which is a primary key and has identity to increment with 1. The column that I compare key data is of course another column. I do all the mapping and remove the mapping between ID columns.
• When I run this I get an error that I cannot insert null in ID. I tried to do a full insert and it works without the ID but when I change to Upsert it fails. The issue was also raised to Microsoft help center and indeed it's a bug regarding the Upsert logic. I was advised to create custom Stored procedure to perform the Upsert using Merge statement as workaround.
• EDIT: info about the source and the sink Source: Source dataset * : data.parquet File path type: Wildcard file path Recursively: Enabled Sink: Sink dataset* : data_table Write Behaviour: Insert ( this is where I want to change to Stored procedure) Bulk Insert table lock: No Table option: None Pre-copy script: delete from db.targettable

All other settings are empty/unticked.

Basically what i want to do is if I have a matching set of keys from the source to the target then it will be an update, otherwise if I don't find matching it will be an insert. Now, I want to use a stored procedure, where I want define what are the key columns to use as an "upsert" in the target table.

I have no experience in writing stored procedures, if some of you kindly explain how this procedure should look like.

Below is what I wrote:

CREATE PROCEDURE [db].[prc_LoadData]
    @column1 NVARCHAR(19),
    @column2 NVARCHAR(10),
    @column3 NVARCHAR(10),
    @column4 DATE,
    @column5 DATE 
AS
BEGIN
    Select * from db.targettable where column1 = @column1,
    Select * from db.targettable where column2 = @column2,
    Select * from db.targettable where column3 = @column3,
    Select * from db.targettable where column4 = @column4,
    Select * from db.targettable where column5 = @column5
END

Answer 1

Stored procedures allow you to execute the set of SQL statements without having to write all those statements each time you want to use these statements.

The syntax will be as follows:

CREATE PROCEDURE <procedure_name> <parameters to be passed>
AS
BEGIN
    -- SQL query
END
GO

MERGE statement uses the data from source table to do operations such as insert, update or delete on the destination table based on a matching condition. Since your requirement is as shown below:

Basically what I want to do is if I have a matching set of keys from the source to the target then it will be an update, otherwise if I don't find matching it will be an insert. Now, I want to use stored procedure, where I want define what are the key columns to use as an "upsert" in the target table.

You can use the MERGE statement inside the stored procedure in the following way to insert if there is no match and update if there is a match in destination table. The following is a small example of using merge statement with stored procedure.

My demo table:

My stored procedure:

CREATE PROCEDURE [dbo].[upsertPlayer_data]
    @gid      int,
    @gamename varchar(100)
AS
    MERGE INTO dbo.player_data AS tgt
    USING (SELECT @gid) AS src (id) ON tgt.Id = src.id

    WHEN MATCHED THEN
        UPDATE        
            SET gname = @gamename

    WHEN NOT MATCHED THEN
        INSERT (id, gname)
        VALUES (@gid, @gamename);

I am using the merge statement using the matching condition as id. When it matches with the id in my destination table, I updated gname

When the matching condition is false, I have inserted the table values.

You can use the format of the above stored procedure and make necessary changes for it to work accordingly with respect to your source and destination.

Answer 2

You are describing a known bug in Azure Data Factory that was fixed in the past week. Microsoft was notified of the issue in May 2022 and began rolling out the fix this week.

Details are available here