Monday, January 31, 2011

how to delete duplicate rows in Microsoft Sql Server

SELECT DISTINCT *
      INTO duplicate_TestDup
     FROM original_TestDup
      GROUP BY ID
      HAVING COUNT(ID) > 1
 
DELETE original_TestDup
      WHERE ID
      IN (SELECT ID
             FROM duplicate_TestDup
          )

INSERT original_TestDup
      SELECT *
         FROM duplicate_TestDup

DROP TABLE duplicate_TestDup

No comments:

Post a Comment