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

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |