IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’tmp_Student’) AND type in (N’U’))
DROP TABLE tmp_Student
GO
CREATE TABLE tmp_Student(stdID INT,stdName VARCHAR(50))
GO
INSERT INTO tmp_Student
SELECT 3,’AAA’ union all
SELECT 4,’BBB’ union all
SELECT 3,’QQQ’ union all
SELECT 5,’XXX’ union all
SELECT 3,’YYY’
GO
select * from tmp_Student –Result before the DELETE action
GO
WITH CTE (stdID, DupCount)
AS
(
SELECT stdID,
ROW_NUMBER() OVER(PARTITION BY stdID ORDER BY stdID) AS DupCount
FROM tmp_Student
)
delete
FROM CTE
WHERE DupCount > 1
go
select * from tmp_Student–Result after the DELETE action