articles

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

Remove Delete Duplicate Records or Rows - SQL Server

mohan kumar6386 07-Jan-2012

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.



Updated 07-Sep-2019
Having around 5 Years experience in .NET domain.

Leave Comment

Comments

Liked By