Post
Ask Question

Remove or Delete duplicate records from Sql server database

How To Remove or Delete duplicate records  from Sql server database 
Three different methods of deleting duplicate records  from sql server database table.

First Method :-->

First of all create table 
 create table Temp	(Id int IDENTITY(1,1),FirstName	varchar(50),Description varchar(50)	)

Insert values 
 insert into Temp(FirstName,Description) Values('name4','dept4')

untitled1.bmp
Perform Operation for deleting Duplicates Records ,
 select * from Temp
SELECT MIN(ID) FROM Temp GROUP BY FirstName,Description

Delete FROM Temp
WHERE ID NOT IN (SELECT MIN(ID)
FROM Temp GROUP BY FirstName,Description) 


untitled2.bmp
Second Method :-->

 WITH DuplicateRecords AS
(
SELECT *,row_number() OVER(PARTITION BY FirstName,Description ORDER BY 

FirstName) 
AS RowNumber FROM Temp
)
Delete FROM DuplicateRecords WHERE RowNumber>1

Third Method :-->
 SELECT DISTINCT * INTO TempTable FROM Temp
GROUP BY FirstName,Description,Id
HAVING COUNT(FirstName) > 1

select Temp WHERE FirstName
IN (SELECT FirstName FROM TempTable)

DELETE Temp WHERE FirstName
IN (SELECT FirstName FROM TempTable)

INSERT Temp SELECT * FROM TempTable
DROP TABLE TempTable

untitled3.bmp