SQL Server: Remove Duplicate records using CTE

Execute below scripts to create a new table by name Sampletable.

CREATE TABLE Sampletable(Emp_Name VARCHAR(50),Emp_ShortName VARCHAR(3),Emp_City VARCHAR(50))

Insert few records

INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Shivakumar', 'SKR','Bangalore')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('KumarKrishnan', 'KKN','Chennai')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Sunil', 'SNL','Shimoga')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Job John', 'JJN','Trivendrum')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Lokesh', 'LKS','Bangalore')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Vinod Raj', 'VRJ','Belgam')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Shivakumar', 'SKR','Bangalore')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Vinod Raj', 'VRJ','Belgam')

Select to check the records of your Sampletable

select * from Sampletable

Your results will be as shown as below.



Execute the below CTE script to check what exactly the query does.

With CTE
As
(
SELECT Emp_Name, Emp_ShortName, Emp_City,ROW_NUMBER() OVER(PARTITION BY Emp_Name, Emp_ShortName, Emp_City ORDER BY Emp_Name) AS DuplicateCount
FROM Sampletable
)
SELECT * FROM CTE WHERE DuplicateCount>1

Results will be like



Once it is confirmed those are the duplicated records, go ahead and run below script to delete duplicated records.

With CTE
As
(
SELECT Emp_Name, Emp_ShortName, Emp_City,ROW_NUMBER() OVER(PARTITION BY Emp_Name, Emp_ShortName, Emp_City ORDER BY Emp_Name) AS DuplicateCount
FROM Sampletable
)
DELETE FROM CTE WHERE DuplicateCount>1