WebTrainingRoom Online Courses
Learn SQL Database Development, MS SQL Tutorial Online

How Remove duplicate records from SQL Table

Find duplicate records from SQL Table and Delete

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

-- 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

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

Cursor inside Stored procedure
Consulting