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