topic Re: upsert option missing in copy activity of microsoft fabric data pipeline in Pipelines

upsert option missing in copy activity of microsoft fabric data pipeline

sarunachalam — Mon, 08 Jul 2024 07:05:47 GMT

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

Re: upsert option missing in copy activity of microsoft fabric data pipeline

NandanHegde — Mon, 08 Jul 2024 08:18:08 GMT

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-Fabric/m-p/3324331

Re: upsert option missing in copy activity of microsoft fabric data pipeline

sarunachalam — Mon, 08 Jul 2024 11:03:07 GMT

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-load/m-p/3262607#M111 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.)

Re: upsert option missing in copy activity of microsoft fabric data pipeline

Anonymous — Fri, 19 Jul 2024 07:55:49 GMT

Hi sarunachalam

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

Regards,

Nono Chen

Re: upsert option missing in copy activity of microsoft fabric data pipeline

sarunachalam — Fri, 19 Jul 2024 08:59:03 GMT

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).