none
Delete Duplicate Records from Table RRS feed

  • Question

  • Hi All,

    I have a requirement from Client, I need to delete duplicate records from one table, Before delete we need to insert records into another table for auditing.

    what is the best approach to achieve this.

    Thanks in Advance.

    Sunday, August 2, 2020 7:37 AM

All replies

  • Hi BHVS:

    a possible solution

    Create table dbo.demo (
    id int, 
    val varchar(100)
    );
    Go
    Drop table if exists dbo.DemoLog; -- SQL Server 2016 or +
    Go
    Insert into dbo.demo (id, val)
    values
    (1,'a'),
    (1,'a'),
    (2,'b'),
    (3,'b'),
    (1,'a'),
    (4,'c'),
    (4,'c'),
    (1,'a'),
    (5,'d'),
    (6,'e');
    go

    identify duplicates using the function: Row_number

    with R as (
    	Select *, ROW_NUMBER() over (partition by id order by (Select null)) as row --(-- here order)
    	From dbo.demo
    )
    Select *
    from R
    

    -- Create and Insert into demoLog
    
    Drop table if exists dbo.demoLog;
    
    with R as (
    	Select *, ROW_NUMBER() over (partition by id order by (Select null)) as row --(-- here order)
    	From dbo.demo
    )
    Select R.id, R.val into demolog 
    from R
    Where row >1;
    
    --  Delete
    
    with R as (
    	Select *, ROW_NUMBER() over (partition by id order by (Select null)) as row --(-- here order)
    	From dbo.demo
    )
    Delete from R
    Where row >1;
    
    go



    Sunday, August 2, 2020 8:09 AM
  • Adding on to Javi's example, you could also create the audit table beforehand and then specify an OUTPUT clause on the delete to perform the insert/delete in one go:

    CREATE TABLE dbo.demo_log (
    	id int, 
    	val varchar(100)
    );
    WITH R AS (
    	SELECT
    		  id
    		, val
    		, ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS row_num
    	FROM dbo.demo
    )
    DELETE FROM R
    OUTPUT deleted.id, deleted.val INTO dbo.demo_log
    WHERE row_num > 1;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, August 2, 2020 11:39 AM
  • Hi All,

    I have a requirement from Client, I need to delete duplicate records from one table, Before delete we need to insert records into another table for auditing.

    what is the best approach to achieve this.

    Thanks in Advance.

    Hi,

    1. Create temp table having only distinct records of current table.

    2. Truncate current table (before doing this, you can also have a backup of original table).

    3. Select all records of temp table in 1), into original table.


    Many Thanks & Best Regards, Jackson Chen



    Sunday, August 2, 2020 3:22 PM
  • Hi

     select distinct * into #tmp From yourtable
           delete from yourtable

    Thanks and regards

    Sunday, August 2, 2020 6:00 PM
  • Hi BHVS,

    Insert records into another table:

    select * into newtable from yourtable

    select * from newtable 

    Delete duplicate records:

    select distinct * into newtable2 from yourtable 
    drop table yourtable
    
    select * from newtable2
    If only a field is repeated instead of a row, please refer to Dan's answer.

    Best Regards
    Echo


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com





    • Edited by Echo Liuz Monday, August 3, 2020 2:30 AM
    Monday, August 3, 2020 2:23 AM
  • Hi BHVS,

    Has your problem been solved? If it is solved, please mark the point that you 
    think is correct as an answer. This can help others who encounter similar problems.

    Best Regards 
    Echo 


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 6, 2020 2:47 AM