Delete duplicate records in sql server

Senthilkumar
12y
4.3k
0
0

Blog

Lets create a table called DuplicateRecords.

 CREATE TABLE DuplicateRecords
(
	ID INT NOT NULL,
	vName VARCHAR(100) NOT NULL,
    iAge INT NOT NULL,
    vAddress VARCHAR(100) NOT NULL,
    vCountry VARCHAR(100) NOT NULL
)
GO

-- Insert few records including some duplicate records.

 INSERT INTO DuplicateRecords(ID,vName,iAge,vAddress,vCountry) VALUES(1,'Senthil',29,'Bangalore','India')
INSERT INTO DuplicateRecords(ID,vName,iAge,vAddress,vCountry) VALUES(2,'Kumar',27,'Chennai','India')
INSERT INTO DuplicateRecords(ID,vName,iAge,vAddress,vCountry) VALUES(3,'Benny',33,'London','United Kingdom')
INSERT INTO DuplicateRecords(ID,vName,iAge,vAddress,vCountry) VALUES(1,'Senthil',29,'Bangalore','India')
INSERT INTO DuplicateRecords(ID,vName,iAge,vAddress,vCountry) VALUES(3,'Benny',33,'London','United Kingdom')
GO

--SELECT * FROM DuplicateRecords

-View the count of duplicates and group by records.

 SELECT ID,vName,iAge,vAddress,vCountry,COUNT(ID) As [Total Records] FROM DuplicateRecords
GROUP BY ID,vName,iAge,vAddress,vCountry

-- Indentify the duplicate records

 SELECT vName,iAge,vAddress,vCountry,COUNT(vName) As [Total Records] FROM DuplicateRecords
GROUP BY vName,iAge,vAddress,vCountry HAVING COUNT(vName) > 1

-- Delete all the duplicate records

 DELETE FROM DuplicateRecords WHERE ID IN
(SELECT ID FROM DuplicateRecords
GROUP BY ID HAVING COUNT(ID) > 1)

-- Delete the additional duplicate records created. If it has more than one record then keep one record and delete remaining duplicate entries created on the table.

WITH DuplcateDelete
AS
(
SELECT ID,'Rank'= DENSE_RANK() OVER(PARTITION BY ID,vName,iAge,vAddress,vCountry ORDER BY NEWID() ASC),vName,iAge,vAddress,vCountry
FROM DuplicateRecords
)
DELETE FROM DuplcateDelete WHERE Rank > 1