locked
How to insert dupliate records from the deleted result set in sql server RRS feed

  • Question

  • HI guys,


    I have query where i have deleted the duplicated records

    2.I want to maintain the resulted duplicate records at the same time in other table for reviewing the duplicate records.

    I have using the below query to duplicate records from the table

    i.e 

    Delete from ##Table1 where %%physloc%% not in (select MIN(%%physloc%%) from 
    ##Table1 group by [ColumnName] )

    but the same time i want to review the same result i.e duplicate record entry ,

    Can some one pls let me know 

    Thaks you!

    Friday, July 17, 2015 10:47 AM

Answers

  • You can use an OUTPUT clause in the DELETE statement to insert the deleted rows into another table for subsequent review.  Something like:

    --create table for deleted rows
    SELECT *
    INTO ##Table1_Deleted
    FROM #Table1
    WHERE 0 = 1;
    
    --delete dups and insert into another table
    DELETE FROM ##Table1 
    OUTPUT deleted.* INTO ##Table1_Deleted
    WHERE %%physloc%% not in (
    	SELECT MIN(%%physloc%%)
    	FROM ##Table1 
    	GROUP BY [ColumnName]
    	);


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Ychinnari Friday, July 17, 2015 11:12 AM
    Friday, July 17, 2015 10:59 AM
    Answerer