Free Web Development Online Tutorials, Learn to Code
How to find and Remove duplicate values from SQL Table, find and delete duplicate rows in sql server

Find duplicate records from SQL Table

In this article you will learn how to find duplicate rows from SQL table based on a key column value, think you have marketing data, you want to delete all duplicate email ids from contact list table.

Delete Duplicate Rows in SQL Database

First we find out all duplicate records and insert them into a temporary table, so we can use as reference while cleaning duplicate records

Here is the SQL Query for finding duplicate records from SQL table.

-- find duplicate data truncate table tbDuplicateData
insert into [tbDuplicateData]
SELECT	Email, COUNT(1) as CNT
FROM	dbo.pankj1
GROUP BY	Email
HAVING	COUNT(1) >1

Now we write a stored procedure with a cursor inside, fetch all data from above temp tbDuplicateData table

Here is the SQL Query for deleting duplicate records from SQL table; this is also an example of how to write cursor inside a stored procedure.

Alter procedure usp_delete_duplicate_data
As
Begin
declare @email_id varchar(1000)  
declare @CountId int 
DECLARE cur_duplicate_contact CURSOR FOR 
SELECT email_id,CountId  
FROM tbDuplicateData 
where  email_id is not null
		 
OPEN cur_duplicate_contact  
FETCH NEXT FROM cur_duplicate_contact 
INTO @email_id,@CountId
WHILE @FETCH_STATUS = 0   
BEGIN 
		   
	BEGIN
		Delete from tbSocialContact
		where 
		email=@email_id and
		nt_id not in (select max(nt_id) from tbSocialContact where email=@email_id)
	END
		 
		FETCH NEXT FROM cur_duplicate_contact 
		INTO @email_id,@CountId
END  
CLOSE cur_duplicate_contact
DEALLOCATE cur_duplicate_contact
END

Whenever the above stored procedure is executed, it will clean all duplicate data matching in temporary table, so make sure you execute both piece of code

Comment
Name
Email
Website
Subscribe
 
Find and Remove duplicate records

SQL job Interview Questions Answers
Course in Demand
Cursor inside Stored procedure
MS SQL Examples | Join MS SQL Course