Remove Duplicate Records From A Table In SQL Server

Nikunj Satasiya
7y
15.7k
0
9

Blog

In this blog i am going to explain how to remove duplicate records from a table in SQL Server. Sometimes while you working with any database like My SQL, MS SQL, Oracal and many others it is required to remove duplicate records from a table. So in this blog i'll show a best way to delete duplicate records from table in Sql Server.

Now, Consider you have a table with following columns.

CREATE TABLE #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,

You can see there is some duplicate records so we will delete all the duplicate records from table by using ROW_NUMBER() in Sql Server. So Write following SQl Query.

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

Now, see affected table

SELECT * FROM #Employee

Summary

In this article, I expose how to remove duplicate records from a table in SQL Server. I hope this blog will helpful to you. Please post your questions or comments about this blog.