CREATE TABLE dbo.Employee
(
EmpID int IDENTITY(1,1) NOT NULL, Name varchar(55) NULL, Salary decimal(10, 2) NULL, Designation varchar(20) NULL
)
WITH TempEmp
(
Name,duplicateCount) AS ( SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name) AS duplicateCount FROM dbo.Employee
)
--PARTITION will gives you duplicate records count
--Now Delete Duplicate Records
DELETE FROM TempEmp WHERE duplicateCount > 1
(
EmpID int IDENTITY(1,1) NOT NULL, Name varchar(55) NULL, Salary decimal(10, 2) NULL, Designation varchar(20) NULL
)
WITH TempEmp
(
Name,duplicateCount) AS ( SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name) AS duplicateCount FROM dbo.Employee
)
--PARTITION will gives you duplicate records count
--Now Delete Duplicate Records
DELETE FROM TempEmp WHERE duplicateCount > 1
No comments:
Post a Comment