[SQL] Elminating duplicate rows from a table

[mendhak]

How to remove all duplicate rows from a SQL Server table

DECLARE @iErrorVar int,

@vchFirstname INT,

@iReturnCode int,

@vchAddress1 varchar(100),

@iCount int,

@chCount char(3),

@nvchCommand nvarchar(4000)


DECLARE DelDupe CURSOR FOR

SELECT COUNT(*) AS Amount,

liCompanyId

FROM CompanyDetails

GROUP BY liCompanyId

HAVING COUNT(*) > 1


OPEN DelDupe

FETCH NEXT FROM DelDupe INTO @iCount,

@vchFirstname

WHILE (@@fetch_status = 0)

BEGIN



SELECT @iCount = @iCount - 1

SELECT @chCount = CONVERT(char(3),@iCount)

--Now we can build our dynamic ROWCOUNT and DELETE statement:

SELECT @nvchCommand = N'SET ROWCOUNT ' + @chCount + ' DELETE FROM CompanyDetails WHERE liCompanyId = ' +  CONVERT(VARCHAR(20),@vchFirstname) 

EXEC sp_executesql @nvchCommand

FETCH NEXT FROM DelDupe INTO @iCount,

@vchFirstName

END

CLOSE DelDupe

DEALLOCATE DelDupe

Substitute 'CompanyDetails' with your table name and liCompanyId with the primary key field of that table.