Deleting duplicate rows from a table in SQL Server.

Deleting duplicate rows from a table in SQL Server:

Method 1:

Insert the distinct rows from the duplicate rows table to new temporary table. Delete data from table which has duplicate rows then insert the distinct rows from the temporary table as shown below.

select distinct * into #temp From tbltest delete from tbltest insert into tbltest                select * from #temp drop table #temp

Method 2:

Remove Duplicate Records by using ROW_NUMBER () or CTE

with CTE as (       select * , row_number() over (partition by CITY order by city) as rank       from tbltest ) delete from CTE where rank > 1