How to Delete SQL Server Database Duplicate Rows from a Table Using a Specified Column List and No Temp Tables

There are many ways in which duplicate data may arrive in a given data system. Prevention is the best method of keeping your databases clean, but when that fails, regardless of the reason, we do have some available techniques for cleaning up the mess. Here is the entire example script file for deleting duplicates:

/*

** This file will delete dupe rows from a table based on a column list. ie., the compound value of the chosen column list is used to determine dupe status and subsequent delete status. We construct some dynamic SQL and use the ROWCOUNT function in determining how many rows to delete.

**

** Revision History:

** -----------------------------------------------------------------------

** Date Name Description

** -----------------------------------------------------------------------

** 07/24/03 DVDS Create

*/


-- declare all variables!

DECLARE @iErrorVar int,

@vchFirstname varchar(30),

@vchLastName varchar(30),

@vchEmailAddress varchar(255),

@iReturnCode int,

@vchAddress1 varchar(100),

@iCount int,

@chCount char(3),

@nvchCommand nvarchar(4000)



-- set initial environment

SET ROWCOUNT 0

SET NOCOUNT ON



-- Build cursor to find duplicated information

DECLARE DelDupe CURSOR FOR

SELECT COUNT(*) AS Amount,

vchFirstName,

vchLastName,

vchAddress1

FROM TestTable

GROUP BY vchFirstName,vchLastName,vchAddress1

HAVING COUNT(*) > 1



OPEN DelDupe

FETCH NEXT FROM DelDupe INTO @iCount,

@vchFirstName,

@vchLastName,

@vchAddress1

WHILE (@@fetch_status = 0)

BEGIN