Monday, July 16, 2012

Finding Duplicates in MS SQL Server

Here is a quick snippet on how to find duplicates:

SELECT a.[dr_id]
      ,COUNT(*) as '# records'
  FROM [LZ].[dbo].[lz_src_address1] a
  where a.sch_type = '23'
  group by a.dr_id, a.sch_type
  having COUNT(*) > 1

You only include the fields that you need to determine if its a duplicate and put them in the GROUP BY clausing.  the HAVING clause tells you if there is more than 1 record.

There are several ways to delete duplicates.  They way you choose will depend on if you have a unique key for each row.  If so, you can delete them with a single statement.  If not, you may need to use a cursor and/or temp tables.