Thursday, June 9, 2016

How to remove duplicate rows from a table in SQL Server

I have reproduced the scenario by using below query for better explanation

insert into [HumanResources].[Department1] --- identity_insert turned off

select * from [HumanResources].[Department]

Below table contain duplicate records

I used row_number function to trace out duplicate rows from the [HumanResources].[Department1]

WITH CTE AS(
   SELECT *,
     RN = ROW_NUMBER()OVER(PARTITION BY departmentid,name,groupname  ORDER BY departmentid )
   FROM [HumanResources].[Department1]
)
DELETE  FROM CTE WHERE RN > 1