Forum Discussion

Responsive Resident

2 years ago

Solved

Remove duplicate values in Fabric Data Warehouse SQL table using Stored Procedure

After creating a Pipeline in Data Factory with success where I fetch data via REST API to Azure SQL Database I wanted to see if I could do the same within Microsoft Fabric and use the new (Synapse) D...

Pipelines

Show and Tell

Tutorial Requests

Yggdrasill
2 years ago
This SP would work if I want to keep all unique rows but in my case I just want to remove duplicates on my key column [id] but with the highest value in [LastSyncedDate]

Your method will still return duplicate id's

I "solved" this by creating a view in the data warehouse which removes the duplicates and then I just removed the last step of the pipeline and I query the view instead of the table...

Yggdrasill

Responsive Resident

2 years ago

I created a stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE RemoveDuplicates
AS
BEGIN
    WITH CTE AS (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY LastDate DESC) AS RowNum
        FROM
            dbo.MyTable
    )

    DELETE FROM CTE
    WHERE RowNum > 1;
END;




GO

Anonymous

Not applicable

2 years ago

Hi Yggdrasill ,
I tried to create a repro with a work around by using the CTAS and the DISTINCT keyword in the stored procedure. I have attached the screenshots for your reference.

1) Created a stored procedure removeDuplicates .