Sunday, July 12, 2015

Remove duplicate records from table - sql server

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

No comments:

Post a Comment

Complete Authentication System in ASP.NET Core using JWT

JWT (JSON Web Token) authentication is widely used for securing APIs in modern applications. In this article, we will build a complete authe...