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 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.
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
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.
|