SQL Server Performance

  • Home
  • Articles
  • Forums
  • Tips
  • Training
  • FAQ's
  • Blogs
  • Software
  • Books
  • About Us
RSS Feeds
Sign in | Join


Article Topics

All Articles
Performance Tuning
Audit
Business Intelligence
Clustering
Reporting Services
SQL Azure
Developer
General DBA
ASP.NET / ADO.NET
SQL Azure

USEFUL SITES :

ASP.NET Tutorials
Windows and SQL Azure Tutorials
Cloud Hosting Magazine
SharePoint Tutorials
Windows Server Help

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...
Latest Articles

A High Level Comparison Between Oracle and SQL Server - Part ...
A High Level Comparison Between Oracle and SQL Server - Part ...
A High Level Comparison Between Oracle and SQL Server - Part ...
A High Level Comparison Between Oracle and SQL Server

More     
 
Latest FAQ's

Add Node to A SQL Server failover Cluster failed with invalid ...
SQL Server Destination remote server error
Setting Up Data And Log Files For SQL Server
Will Check Constraints Improve Database Performance?

More     
   
Latest Software Reviews

dbForge Review
Spotlight on ApexSQL Diff - Server-based database comparison tool ...
Spotlight on ApexSQL Data Diff - Server-based database comparison tool ...
Spotlight on ApexSQL Doc 2008

More     

articles >> developer >> How to Delete SQL Server Database Duplicate ...

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

By : David VanDeSompele
Jul 06, 2004

Page 2 / 3

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


<< Prev Page     Next Page>>    








C# Help and Tutorials | PHP MySQL Tutorial | Sharepoint Tutorial | Azure Tutorial | Cloud Hosting Magazine | ASP.NET Tutorials | Windows Server Help | Windows Phone Pro | Silverlight Ace | Visual Studio Tutorials | Home | Peformance Articles | Audit Articles | Business Intelligence Articles | Clustering Articles | Developer Articles | Reporting Services Articles | DBA Articles | ASP.NET / ADO.NET Articles | SQL Server Training Videos | DBA FAQ's | Developer Peformance FAQ's | DBA Peformance FAQ's | Developer FAQ's | Clustering FAQ's | Error Messages | Audit Tool Reviews | Backup Tool Reviews | Coding Tool Reviews | Compare Tool Reviews | Documentation Tool Reviews | Design Tool Reviews | Monitoring Tool Reviews | Log Tool Reviews | Reporting Tool Reviews | Clustering Tool Reviews | Security Tool Reviews | Change Management Tool Reviews | Remote Access Tool Reviews | Book Reviews | Security Tool Reviews | ADO.NET / ASP.NET | Administration | Analysis/OLAP Services | Application Development | Configuration | Components | ETL | Hardware | High Availability | Hints | Index | Misc | Operating Systems | Performance Tuning | Replication | T-SQL | Views


              © 2010 Jude O'Kelly. All rights reserved