SQLWebpedia

Even if you have variable number of duplicates, this script will delete them all. It uses SQL 2005's new TOP feature in DELETE clause. The () are required now, although still works without them in SELECT statements.

DECLARE @cnt int, @custID as int

DECLARE dupCursor CURSOR FAST_FORWARD
FOR SELECT CustomerID, COUNT(CustomerID) AS Cnt
    FROM tmpCustomer
    GROUP BY CustomerID
    HAVING COUNT(CustomerID) > 1

OPEN dupCursor

FETCH NEXT FROM dupCursor
INTO @custID, @cnt

WHILE @@FETCH_STATUS = 0
BEGIN
    DELETE Top (@cnt-1)
    FROM tmpCustomer
    WHERE CustomerID = @custID
   
    FETCH NEXT FROM dupCursor
    INTO @custID, @cnt
END

CLOSE dupCursor
DEALLOCATE dupCursor

Another method with a CTE:

/* Delete Duplicate records */
WITH CTE (PrimaryKeyColumn1,PrimaryKeyColumn2, DuplicateCount)
AS
(
SELECT PrimaryKeyColumn1,PrimaryKeyColumn2,
ROW_NUMBER() OVER(PARTITION BY PrimaryKeyColumn1,PrimaryKeyColumn2 ORDER BY PrimaryKeyColumn1) AS DuplicateCount
FROM DuplicateRecordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO