Home > DeveloperSection > Articles > Remove Delete Duplicate Records or Rows - SQL Server

Remove Delete Duplicate Records or Rows - SQL Server


Database Database 
Ratings:
0 Comment(s)
 4528  View(s)
Rate this:

Remove Delete Duplicate Records or Rows - SQL Server

Hey Guys!! In this article, I will be explaining you, how to delete the duplicate records from the database table.

 

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.


Remove Delete Duplicate Records or Rows - SQL Server

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.

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


Now write this query to delete duplicate rows.

DELETE FROM dbo.Employees

WHERE ID NOT IN (SELECT MIN(ID)

FROM dbo.Employees GROUP BY FirstName,Department)


This should remove all duplicate records from table.


Second Method:-

Remove duplicate rows/Records using temporary table.


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

SELECT DISTINCT * INTO TempTable FROM dbo.Employees

GROUP BY FirstName,Department

HAVING COUNT(FirstName) > 1

 

DELETE dbo.Employees WHERE FirstName

IN (SELECT FirstName FROM TempTable)

 

INSERT dbo.Employees SELECT * FROM TempTable

DROP TABLE TempTable

 

Remove Delete Duplicate Records or Rows - SQL Server

 

And result will be as shown.

Just post back a reply if this article was useful for you. Thanks for reading my article. Happy Coding!! Explore more and more as much as possible.


Don't want to miss updates? Please click the below button!

Follow MindStick