Thursday 11 October 2012

Remove duplicate records from a table in SQL Server

Sometimes we required to remove duplicate records from a table although table has a UniqueID Column with identity. In this article, I would like to share a best way to delete duplicate records from a table in SQL Server.

Suppose we have below Employee table in SQL Server.

 CREATE TABLE dbo.Employee
( 
   EmpID int IDENTITY(1,1) NOT NULL, 
   Name varchar(55) NULL, 
   Salary decimal(10, 2) NULL, 
   Designation varchar(20) NULL
 ) 

The data in this table is as shown below:



Remove Duplicate Records by using ROW_NUMBER()


 WITH TempEmp (Name,duplicateRecCount)
AS
(
SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name) 
AS duplicateRecCount
FROM dbo.Employee
)
--Now Delete Duplicate Records
DELETE FROM TempEmp
WHERE duplicateRecCount > 1 
 
 
--See affected table
Select * from Employee 
 
 
 
 
For more help about ROW_NUMBER(), please follow the MSDN link.
 
 

No comments :