Delete duplicate entry from table
Posted by codingsense on December 26, 2008
While working on a project i found that my tables were loading with duplicate entries and i wanted to remove the duplicate entries. I googled for the solution but found none. I think there is no option given in MSSQL for this issue. So i made my own logic to remove the duplicate entries and it worked fine. I have even tested this with 3 million records and it take just few seconds.
I create a procedure for this issue just have a look.
Create PROCEDURE RemoveDuplicateDomain AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --Create Temp Table for holding the duplicate values Create table #TempTable ( DomainName varchar(200)); --Insert all duplicate values from into the temptable insert into #TempTable(DomainName) select EmailID from EmailA Group by EmailID having count(emailid) > 1; --Delete the selected duplicate entry from the main table Delete from EmailA where EmailID in (select DomainName from #TempTable); --Insert back the single entry into the main table from temp table Insert into EmailA(emailid) Select DomainName from #TempTable; --Drop the temp table drop table #TempTable ; END GO
If you know any better idea please post a comment
Happy Learning 🙂