Eliminate Duplicates

,

This will find and delete all duplicate rows. You can refine the granularity of uniqueness by including more columns than just the Primary Key. If you are picky about which rows you want to delete, don't use this. Instead use the 'Find Duplicates' script I just submitted and then delete manually.

DECLARE @Dups int, @fld1 int, @fld2 int
DECLARE Dups CURSOR FOR
SELECT fld1, fld2, COUNT(*) - 1 AS Duplicates
FROM Table1
GROUP BY fld1, fld2
HAVING COUNT(*) > 1
OPEN Dups
WHILE(1=1)
BEGIN
FETCH NEXT FROM Dups INTO @fld1, @fld2, @Dups
IF @@FETCH_STATUS < 0 BREAK
SET ROWCOUNT @Dups
DELETE Table1 WHERE fld1 = @fld1 And fld2 = @fld2
SET ROWCOUNT 0
END
DEALLOCATE Dups

Rate

Share

Share

Rate