Delete Duplicates

I have the following stored procedure to delete duplicate records.

---------------------------------------------------------------------------------------------------------------------------------

CREATE proc [dbo].[spdeletedupes]

as

Begin

drop table dupeoh

create table dupeoh
(invoicenumber varchar(10) not null default 0,
customernumber varchar(10) not null default 0,
count smallint default 0,
linenumber smallint default 0,
id int default 0)

declare @period datetime
set @period= (select transactiondate from maxtransactiondate)

insert into dupeoh
select invoicenumber,customernumber, count(*)as Count,0,0
from orderheader
where transactiondate=@period
group by invoicenumber,customernumber
having count(*)>1

update dupeoh
set id = (select min(id)
            from orderheader
            where transactiondate=@period
            and dupeoh.invoicenumber=orderheader.invoicenumber
            and dupeoh.customernumber=orderheader.customernumber)


delete from orderheader
where id in (select id from dupeoh)


delete From dupeoh


insert into dupeoh
select invoicenumber,customernumber, count(*)as Count,linenumber,0
from orderlines
where transactiondate=@period
group by invoicenumber,customernumber,linenumber
having count(*)>1

update dupeoh
set id = (select min(id)
            from orderlines
            where transactiondate=@period
            and dupeoh.invoicenumber=orderlines.invoicenumber
            and dupeoh.customernumber=orderlines.customernumber
            and dupeoh.linenumber=orderlines.linenumber)

delete from orderlines
where id in (select id from dupeoh)

End

---------------------------------------------------------------------------------------------------------------------------------

The procedure works fine, but more and more lately the procedure needs to be run more than once before all of the duplicates are removed.  I want to be able to continue to run this procedure until no more duplicates are found but I am not sure of the best way to go about it.Start Free Trial