Remove Delete Duplicate Records Or Rows - Sql Server

Remove or Delete duplicate records or rows from ms sql server database table.

In this post i am going to describe different methods of deleting duplicate records or rows from sql server database table.

I am using Employees table with FirstName and Department columns.

First Method.

Delete duplicate records/rows by creating identity column.

duplicate records in table looks like shown in first image.

First of all we need to create a identity column in our table by using code mentioned below.

And table will look like image on the left.

1ALTER TABLE dbo.Employees ADD ID INT IDENTITY(1,1)

Now write this query to delete duplicate rows.

1DELETE FROM dbo.Employees

2WHERE ID NOT IN (SELECT MIN(ID)

3FROM dbo.Employees GROUP BY FirstName,Department)

This should remove all duplicate records from table.


Second Method.

Delete duplicate records using Row_Number()

If you do not want to make any changes in table design or don't want to create identity column on table then you can remove duplicate records using Row_Number in sql server 2005 onwards.

for this write below mentioned code and execute.

1WITH DuplicateRecords AS

2(

3SELECT *,row_number() OVER(PARTITION BY FirstName,Department ORDER BY

4 

5FirstName)

6AS RowNumber FROM dbo.Employees

7)

8DELETE FROM DuplicateRecords WHERE RowNumber>1

This should remove all duplicate records from table.


Third Method.

Remove duplicate rows/Records using temporary table

Use below mentioned code to delete duplicates by moving them to temporary table using DISTINCT.

1SELECT DISTINCT * INTO TempTable FROM dbo.Employees

2GROUP BY FirstName,Department

3HAVING COUNT(FirstName) > 1

4 

5DELETE dbo.Employees WHERE FirstName

6IN (SELECT FirstName FROM TempTable)

7 

8INSERT dbo.Employees SELECT * FROM TempTable

9DROP TABLE TempTable

And result will be as shown.

Have fun.