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.
1
ALTER TABLE dbo.Employees ADD ID INT IDENTITY(1,1)
Now write this query to delete duplicate rows.
1
DELETE FROM dbo.Employees
2
WHERE ID NOT IN (SELECT MIN(ID)
3
FROM 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.
1
WITH DuplicateRecords AS
2
(
3
SELECT *,row_number() OVER(PARTITION BY FirstName,Department ORDER BY
4
5
FirstName)
6
AS RowNumber FROM dbo.Employees
7
)
8
DELETE 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.
1
SELECT DISTINCT * INTO TempTable FROM dbo.Employees
2
GROUP BY FirstName,Department
3
HAVING COUNT(FirstName) > 1
4
5
DELETE dbo.Employees WHERE FirstName
6
IN (SELECT FirstName FROM TempTable)
7
8
INSERT dbo.Employees SELECT * FROM TempTable
9
DROP TABLE TempTable
And result will be as shown.
Have fun.
Thanks
Suneel Kumar
Responses
0 Respones to "Remove Delete Duplicate Records or Rows - SQL Server"
Post a Comment