Mike Mead

Finding and removing duplicate records (MS SQL)

Written by Mike

Aug 15, 2012

SQL

Finding and removing duplicate records (MS SQL)

Here are two very useful T-SQL queries for finding and removing duplicate records within a selected table.

Make sure you backup your table/database before running the query to remove duplicates.

Finding duplicate records

select Field1, Field2, Field3, count(*) from Table group by Field1, Field2, Field3 having count(*) > 1 --Use as many fields as possible for selecting duplicate records.

Removing duplicate records

delete a from (select DupeRank = row_number() OVER (Partition By Field1, Field2, Field3 order by Field1, Field2, Field3 asc) from Table) a where DupeRank > 1 --Use the same fields as in the first (finding duplicates) query.
comments powered by Disqus