How to Delete Duplicate Records from an SQL Server Table

By: Editor
March 09, 2010

Introduction

Deleting duplicate records from an SQL Server table can sometime become a very tedious activity especially in scenarios when you don’t have a Primary Key defined for the source table. In this article we will take advantage of Common Table Expression (CTE) feature of SQL Server 2005 to delete duplicate records from the table.

What is Common Table Expression (CTE)?

A common table expression (CTE) can be considered as a temporary result set which is defined within the execution scope of a single INSERT, UPDATE, SELECT OR DELETE statements. It is much similar to a derived table. However, a CTE can be self referenced multiple times within the scope of the same query.

Deleting Duplicate Records Using Common Table Expression (CTE) Feature of SQL Server 2005

Let us go through the below mentioned example to remove duplicates from a table which doesn’t have a Primary Key defined.

 /* Sample Script to Deleting Duplicate Records Using CTE */
USE TempDB
GO

/* Drop EmployeeDetails Table if already exists */
IF OBJECT_ID (N'dbo.EmployeeDetails', N'U') IS NOT NULL
    DROP TABLE dbo.EmployeeDetails;
GO 

/* Create EmployeeDetails Details Table*/
CREATE TABLE EmployeeDetails
(
EmpID       INT IDENTITY (1,1),
Name        VARCHAR(25),
Age         INT
)
GO

/* Insert Script is using Row Value Constructor Feature of SQL Server 2008*/

INSERT
INTO EmployeeDetails
VALUES
       ('Lilly', 25)
      ,('Lucy',  25)
      ,('Lilly', 25)
      ,('Mary',  26)
      ,('Mariam',26)
      ,('Mary',  26)
      ,('Lisa',  27)
GO

/* Using Common Table Expression Feature to Delete Duplicate Records */
WITH SampleCTE
AS
(
     
SELECT ROW_NUMBER () OVER ( PARTITION BY NAME, AGE ORDER BY EmpID) AS RNUM
      FROM EmployeeDetails

)

DELETE FROM SampleCTE WHERE RNUM > 1
GO

/* Employee Details without Duplicates */
SELECT * FROM EmployeeDetails
GO



The below mentioned snippet shows that the duplicate records are removed successfully.

Deleting Duplicate Records from SQL Server Table Using Common Table Expressions Feature of SQL Server 2005

References: http://msdn.microsoft.com/en-us/library/ms190766.aspx

Conclusion

In this article you have seen how easily you can remove duplicate records from a table using the Common Table Expression (CTE) feature of SQL Server 2005.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…




Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."