s

mssql query to remove duplicate from a table edit button Edit

author
Murugan Andezuthu Dharmaratnam | calendar 03 November 2020 | 2063

We will create a temporary table #Temp, Insert a few records and we will write an mssql query to remove duplicates from the table.

Create A Temporary Table

we will create a temporary table #Temp which has two columns Id and Name

create table #Temp(
	Id int,
	[Name] nvarchar(64)
)
               
                    

Add data to the temporary table

Insert rows into the temporary table

insert into #Temp(id,Name) values(1,'Murugan'),(2,'Anand'),(3,'Murugan'),(4,'Murugan')
               
                    

Remove Duplicates

Here is the simple query to remove duplicates rows from the table.

delete from #Temp where Id IN (
select Id from (select *,ROW_NUMBER() over( partition by [Name] order by Id) as RN from #Temp ) s
where RN > 1 )