INF: Removing Duplicate Rows from an Existing Table in SQL (70956)

SUMMARY

The following script can be used to remove duplicate rows from a SQL Server table:

   SELECT DISTINCT *
      INTO duplicate_table
      FROM original_table
      GROUP BY key_value
      HAVING COUNT(key_value) > 1

   DELETE original_table
      WHERE key_value
      IN (SELECT key_value
             FROM duplicate_table)

   INSERT original_table
      SELECT *
         FROM duplicate_table

     DROP TABLE duplicate_table
				

When executed, this script moves one instance of any duplicate row in the original table to a duplicate table. It then deletes all rows from the original table that also reside in the duplicate table. Next, the rows in the duplicate table are moved back into the original table. Finally, the duplicate table is dropped.