upsert option missing in copy activity of microsoft fabric data pipeline

[sarunachalam]

Hi 

I don't see an option to upsert (insert new rows and update existing rows based on PK) in copy activity or in data flow gen2. Is there an alternative how I can acheive this? I am using Azure SQL Warehouse in Fabric as target.

Regards

Subbu

[sarunachalam · Accepted solution]

Hi Thanks for the response.

I used a work around, I load the data to a stage table and then created a procedure to left join the stage table & actual table to compare & insert missing records and update the existing records with 2 seperate SQL statements (first for update and then for insert). 

[v-nuoc-msft]

Hi @sarunachalam 

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

[sarunachalam]

Thanks for your quick response. I am looking for upsert option in data pipelines or data flow gen2. I understand MERGE command is not available in Azure SQL Warehouse as of now. But in one of the thread in the link https://community.fabric.microsoft.com/t5/Data-Pipelines/Fabric-Data-Factory-Pipeline-Incremental-lo... I understand this is now enabled in a private view This is the part of the comment I read. Hence wanted to check if there is a way to get the upsert (Its in private preview, please ping me with your workspace id, and we can enable it for you along with instructions.)

[NandanHegde]

Based on my knowlegde, Upsert/Merge is not yet directly supported for fabric warehouse.

So you would have to copy the data into a staging table and then leverage the below logic to upsert into destination table

https://community.fabric.microsoft.com/t5/Data-Pipelines/Datawarehouse-MERGE-command-not-working-in-...