Remove or Delete duplicate records from Sql server database
REmove Duplicate Records from Sql server,How to remove Duplicate records in 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')
|
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)
|
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
|