Forum Discussion

Alaahady's avatar
Alaahady
Helper II
1 year ago
Solved

Upsert data to Lakehouse Table using copy data

I need to transfer table from on-premises sql server to datalake using incremental update (upsert) but it failed, "Operation on target failed, Upsert is not a supported table action for Lakehouse Tab...
data engineering
v-dineshya's avatar
v-dineshya
Community Support
1 year ago

Hi Alaahady ,

Thank you for reaching out to the Microsoft Community Forum.

 

You are trying to perform an incremental upsert from an on-premises SQL Server to a Lakehouse Table using the Copy activity, but encountered the error as “Upsert is not a supported table action for Lakehouse Table.”

 

Please try below things to fix the issue.

 

1. Dataflow Gen2 does not support Lakehouse Tables directly for upsert via Copy activity. you can Load data into a staging table (Fabric SQL) and use Dataflow Gen2 to manage incremental logic and then merge into Lakehouse using a notebook or pipeline.

 

2. Use Notebooks for Custom Upsert Logic, Load data into Lakehouse using Copy activity, Use a Spark notebook to compare and merge data (upsert logic).

 

Note: This allows schema evolution and conditional updates.

 

3. Compare schemas in a Spark notebook. Write changes to a staging configuration (CSV or SQL table). Use Fabric Pipeline to read changes and execute ALTER TABLE or MERGE statements in the Warehouse.

 

4. Use Power BI Premium with Hybrid Tables and then Configure Incremental Refresh with Real-Time Data and then check DirectQuery is enabled for recent partitions.

 

Please refer community thread.

Solved: Incremental refresh og selected refresh using data... - Microsoft Fabric Community

 

I hope this information helps. Please do let us know if you have any further queries.

 

Regards,

Dinesh