Naveen's Weblog

Bridge to future

Delete duplicate entry from table

Posted by codingsense on December 26, 2008


Hi friends,

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 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: