.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Delete Duplicate Records in SQL Server 2005/2008

Posted By:Rajeev Kumar Mehta       Posted Date: November 02, 2010    Points: 50    Category: DataBase    URL: http://www.dotnetspark.com  

This article illustrates how to delete duplicate records in SQL Server 2005/2008
 

Step (1). Login into SQL Server

Step (2). Run the following query

WITH CTE (Col1,Col2, DuplicateCount)
AS
(
SELECT Col1,Col2,
ROW_NUMBER() OVER(PARTITION BY Col1,Col2 ORDER BY Col1,Col2) AS DuplicateCount
FROM Table1
)
DELETE
FROM CTE
WHERE DuplicateCount > 1

where Col1 and Col2 are the composite key for the Table1.

Note - This query does not require that Table1 should contain a primary key or Identity column.


All queries are welcome !!
Rajeev Kumar Mehta

EMail - mikkykumar@yahoo.com,mikkykumar@gmail.com
 Subscribe to Articles

     

Further Readings:

Responses
Author: NITIN         Company URL: http://www.dotnetspark.com
Posted Date: November 24, 2010

HI
It would be highly appriciated if you can ellaborate in more detail with some theortical concet...


Thanks
nitin

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend