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
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
You may be interested in following post