Article

How to remove duplicate rows from a table in SQL Server

05 Jun 2015 Kamal Pratap
0 Comments 1470 Views



Sometime we require to remove duplicate records from database table. To remove duplicate records we use ROW_NUMBER() to identify duplicate records.

Follow these steps to delete duplicate recodrs

    CREATE TABLE dbo.STUDENT
    ( 
    StudentID int IDENTITY(1,1) NOT NULL, 
    Name nvarchar(50) NULL, 
    RollNumber decimal(10, 2) NULL, 
    Class nvarchar(50) NULL
    ) 
    
    insert into dbo.STUDENT (Name,RollNumber,Class)values('Andy',100,'MCA')
    insert into dbo.STUDENT (Name,RollNumber,Class)values('Andy',100,'MCA')
    insert into dbo.STUDENT (Name,RollNumber,Class)values('Bill',200,'BCA')
    insert into dbo.STUDENT (Name,RollNumber,Class)values('Bill',200,'BCA')
    insert into dbo.STUDENT (Name,RollNumber,Class)values('Bill',200,'BCA')
    insert into dbo.STUDENT (Name,RollNumber,Class)values('Chris',300,'MBBS')    
    
    Select * from Student

How to remove duplicate rows from a table in SQL Server

We have a table with duplicate records so remove duplicate records by using ROW_NUMBER()

    WITH TempEmp (Name,RollNumber,Class,duplicateRecCount)
		AS
		(		
		SELECT Name,RollNumber,Class,ROW_NUMBER() OVER(PARTITION by Name,RollNumber,Class ORDER BY Name) 
		AS duplicateRecCount
		FROM dbo.STUDENT
		)
	--Now Delete Duplicate Records
	
	DELETE FROM TempEmp WHERE duplicateRecCount > 1
	
	Select * from Student

 

How to remove duplicate rows from a table in SQL Server

How to remove duplicate rows from a table in SQL Server

Kamal Pratap

I have 6+ years experience in .Net technologies like Asp.Net, C#, WCF, Web Services, SQL Server, Ajax, LinQ. Currently I am working in Netcarrots Loyalty Services as a Software Developer.

Comments

No coments found to display!

Leave a Comment