Introduction


Here I will explain how to Delete Duplicate Record or Rows from Table in SQL Server. I am not going in detail or background of the article its a common problem which occurs time to time with developers so here i just explain how solve your problem.

SQL query to delete duplicate rows


create a table like this,

create table Emp(empid int,name varchar(20))

Table Emp

empid name
1 abc
1 def
2 abc
2 abc

Enter some random or duplicate value in table:

Method 1

  1. select distinct * into ‪#‎tmptbl‬ From Emp
  2. delete from Emp
  3. insert into Emp
  4. select * from #tmptbl drop table #tmptbl
If you want to consider only few columns in a table for duplication criteria to delete rows then Method 1 will not work (in example, if EMP table has more than 2 columns and delete rows if empid and name repeats more than one time).

Method 2

You can do with CTE (Common Table Expression).
  1. WITH cte AS (
  2. SELECT empid , name ,
  3. row_number() OVER(PARTITION BY empid , name order by empid ) AS [rn]
  4. FROM dbo.Emp
  5. )
  6. DELETE cte WHERE [rn] > 1
If you want to delete all the rows if the selected columns repeated more than 1 time then use below query.

Method 3
  1. delete from Emp where empid in(select empid from Emp group by empid having count(*) >1)