Post
Ask Question

Delete Duplicate Records From A Table In SQL Server

In this blog, I would like to share a simple way to delete the duplicate record from a table, using ROW_NUMBER ()

Step 1

Table structure 
  1. CREATE TABLE #Employee (  
  2.   
  3. EmpID INT IDENTITY(1001, 1)  
  4.   
  5. , EmpName VARCHAR(200)  
  6.   
  7. , EmpSalary INT  
  8.   
  9. )  

Step 2

Query for inserting record into an Employee table 
  1. INSERT# Employee(EmpName, EmpSalary) Values('Vijay P', 100000)  
  2. INSERT# Employee(EmpName, EmpSalary) Values('Vijay P', 100000)  
  3. INSERT# Employee(EmpName, EmpSalary) Values('Vijay P', 100000)  
  4. INSERT# Employee(EmpName, EmpSalary) Values('Praveen P', 90000)  
  5. INSERT# Employee(EmpName, EmpSalary) Values('Praveen P', 90000)  
  6. INSERT# Employee(EmpName, EmpSalary) Values('Ranjith R', 80000)  
  7. INSERT# Employee(EmpName, EmpSalary) Values('Ranjith R', 80000)  
  8. INSERT# Employee(EmpName, EmpSalary) Values('Ranjith R', 80000)  
  9. INSERT# Employee(EmpName, EmpSalary) Values('Charan B', 50000)  
  10. INSERT# Employee(EmpName, EmpSalary) Values('Charan B', 50000)  
  11. INSERT# Employee(EmpName, EmpSalary) Values('Charan B', 50000)  
  12. INSERT# Employee(EmpName, EmpSalary) Values('Venkata T', 50000)  

Step 3

#Employee table contains duplicated records given below.

SELECT * FROM #Employee

   
Step 4
 
Delete the duplicate record, using ROW_NUMBER () 
  1. SELECT EmpID, EmpName, EmpSalary, ROW_NUMBER() OVER(PARTITION BY EmpName, EmpSalary ORDER BY EmpID) AS RowNumber INTO# EmployeeFinal  
  2. FROM# Employee  
  3. DELETE  
  4. FROM# Employee  
  5. WHERE EmpID IN(SELECT EmpID FROM# EmployeeFinal WHERE RowNumber > 1)  

Step 5 

After the deletion of the duplicate rows from the #Employee table, it displays, as shown below.