Update table with SQL MERGE issue duplicates: help needed with combining SQL deduplicate & update

Hi Vogels 
Thanks for using Fabric Community.
You are doing right. 

Instead of copying and deleting the table, perform deduplication onLarge_Table_Previous_Daysbefore merging. This ensures unique rows are fed into the MERGE statement. You can achieve this using the ROW_NUMBER function to assign a unique sequence number based on a specific order:

-- Deduplicate Large_Table_Previous_Days
SELECT *,
  ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LOAD_DATE DESC) AS row_num
FROM Large_Table_Previous_Days

-- Use only the first row (latest LOAD_DATE) for each ID in the merge
WHERE row_num = 1

 
Modify your MERGE statement to use the deduplicated Large_Table_Previous_Days as the source:

-- Deduplicate Large_Table_Previous_Days
SELECT *,
  ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LOAD_DATE DESC) AS row_num
FROM Large_Table_Previous_Days
WHERE row_num = 1

-- Merge deduplicated data into Large_Table
MERGE INTO Large_Table AS target
USING (
  -- Deduplicated result from the previous step
) AS source
ON (target.ID = source.ID)

WHEN MATCHED THEN
  UPDATE SET
    target.LOAD_DATE = source.LOAD_DATE,
    target.ID = source.ID,
    target.Content = source.Content

WHEN NOT MATCHED THEN
  INSERT (LOAD_DATE, ID, Content)
  VALUES (source.LOAD_DATE, source.ID, source.Content)

Replace Large_Table, Large_Table_Previous_Days, and the column names (ID, LOAD_DATE, Content) with your actual table and column names.

This code performs the following steps:

1. Deduplicates Large_Table_Previous_Days:
   • It assigns a unique row_num to each row within each group of the same ID, ordered by descending LOAD_DATE.
   • Only the first row (latest LOAD_DATE) for each ID is kept, ensuring unique data.
2. Merges deduplicated data:
   • The deduplicated result is used as the source in the MERGE statement.
   • ON clause matches rows based on the same ID in both tables.
   • WHEN MATCHED updates existing rows in Large_Table.
   • WHEN NOT MATCHED inserts new rows from the source.

This combined code should prevent duplicate row errors and ensure only new or changed entries are updated in your large table.

While this is one approach , if you want to perform an upsert (update or insert) operation on a table, you can use Dataflow Gen2 here. Incrementally amassing data in a data destination requires a technique to load only new or updated data into your data destination. This technique can be done by using a query to filter the data based on the data destination. The source will be the Large_Table_Previous_Days and the target table will be Large_Table. For more information refer to this document:
https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2


Hope this helps. Please let us know if you have any further queries.