Search duplicate records from SQL table

In this article you will learn how to search 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.

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

Delete Duplicate Rows in SQL Database

Here is the SQL Query for searching duplicate records from SQL table and inserting them into another 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

In above example, i have written a cursor, if you find it difficult to understand, learn sql cursor code in more details.

You may be interested in following post

 
Hire SQL Developer
Search and Remove duplicate records
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
Cursor inside Stored procedure
MS SQL Examples | Join MS SQL Course