merge or update/insert lakehouse table from another lakehouse table

[Anonymous]

How merge or update/insert lakehouse table from another lakehouse table

I am getting error message:

Data Manipulation Language (DML) statements are not supported for this table type in this version of SQL Server.

[v-prasare]

Hi @Anonymous,

Thanks for reaching out to MS fabric community support. 

@nilendraFabric Thanks for your prompt response here.

@Anonymous  , as we haven’t heard back from you, we wanted to check in to see if the resolution provided by our community member helps?

If you’re still facing any issues or have additional questions, please don’t hesitate to let us know.

 Looking forward to your feedback!

Thanks,

Prashanth Are

MS Fabric community support.

Did we answer your question? Mark post as a solution, this will help others identify similar issues easily!

If our response(s) assisted you in any way, don't forget to drop me a "Kudos"

[nilendraFabric]

Hello @Anonymous 

The `MERGE` statement is not natively supported in Microsoft Fabric’s Data Warehouse as of now. Users need to rely on PySpark or SQL workarounds for similar functionality

details in next comment.

[Anonymous]

This is link from azure not fabric. Last time I tried merge didn't work in fabric.

[nilendraFabric · Accepted solution]

Hello @Anonymous 

Try this 

The `MERGE` statement is not natively supported in Microsoft Fabric’s Data Warehouse as of now. Users need to rely on PySpark or SQL workarounds for similar functionality

from delta.tables import DeltaTable

# Define source and target
target_table = DeltaTable.forName(spark, "Lakehouse.TargetTable")
source_df = spark.read.table("Lakehouse.SourceTable")

# Perform the merge
target_table.alias("target").merge(
source=source_df.alias("source"),
condition="target.key = source.key"
).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

https://aventius.co.uk/2024/03/28/microsoft-fabric-using-pyspark-to-dynamically-merge-data-into-many-tables

Currently, the `MERGE` statement is not supported in Fabric’s Data Warehouses. As a workaround:
• Use a combination of `ROW_NUMBER()` for deduplication and `INSERT`/`UPDATE` statements to achieve similar functionality:

WITH DeduplicatedSource AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LOAD_DATE DESC) AS row_num
FROM SourceTable
WHERE row_num = 1
)
-- Merge logic
MERGE INTO TargetTable AS target
USING DeduplicatedSource AS source
ON target.ID = source.ID
WHEN MATCHED THEN UPDATE SET target.Column = source.Column
WHEN NOT MATCHED THEN INSERT (Column) VALUES (source.Column);

https://community.fabric.microsoft.com/t5/Data-Engineering/Update-table-with-SQL-MERGE-issue-duplicates-help-needed-with/m-p/

https://community.fabric.microsoft.com/t5/Data-Pipeline/Datawarehouse-MERGE-command-not-working-in-Fabric/m-p/3324331

Thanks