Todd Carrico

This is some code I use for removing duplicate rows from a table based on Primary Keys.  It could be easily modified for other scenarios.  The end result is a clean table.

CREATE TABLE #Dups(
    PkUniquevalue INT IDENTITY(0,1) NOT NULL PRIMARY KEY,
    cnt INT NOT NULL,
    BannerID INT
)

INSERT INTO #Dups ( cnt, BannerID )
SELECT
    COUNT(*),
    BannerID
FROM dbo.Banner
GROUP BY BannerID
HAVING COUNT(*) > 1

IF @@ROWCOUNT > 0
BEGIN

    DECLARE @PkUniquevalue INT
    DECLARE @RowsToKill INT 
    DECLARE @BannerID int 

    SET @PkUniquevalue = 0 

    WHILE (1=1) 
    BEGIN 

        SELECT TOP 1 
            @RowsToKill = cnt - 1, 
            @BannerID = BannerID, 
            @PkUniquevalue = PkUniquevalue 
        FROM #Dups 

        IF @@ROWCOUNT > 0 
        BEGIN 
            SET ROWCOUNT @RowsToKill 
            DELETE FROM dbo.Banner
            WHERE BannerID = @BannerID 
            SET ROWCOUNT 0 
        END 
        ELSE 
        BEGIN 
            BREAK 
        END 
        DELETE FROM #Dups WHERE PkUniquevalue = @PkUniquevalue 

    END    
END