For Deleting Duplicate Row???????

Hi,

I want to delete duplicate row from a very big table. Actually this table is used by a SP, and it's a very important. Due to duplicate record entry it's falling. I use bellow method for discarding the dulicate record.

PLz tel me it's the most efficent way to this job or u have some other way

1. I drop the primary key

2. Then I let all the duplicate record came into the table

3. then I removed them by using Group by clause and setting rowcount(1 - group by count).

4. Put primary key back and update the statistics.

Code is

 If Exists (select * from SYSINDEXES where name='PrimaryKey' and id=Object_id('AdjustmentTransactions'))
 DROP INDEX AdjustmentTransactions.PrimaryKey

Insert into AdjustmentTransactions
(UrnABS, UrnBar, .................Description)
Select a.UrnAbs,
       a.UrnBar,
       a.TxnUrn.....................
       a.Description
from TxnProcess a
where a.InsUpFlag = 'I'
  and a.Processed = 'N'
  and ASCII(b.TxnType) = 65
 
Set @row_count=0
Declare dup_cursor cursor for
 Select UrnBAR,TxnUrnBarPat,count(*) counts from AdjustmentTransactions
 group by UrnBAR,TxnUrnBarPat having count(*) > 1
Open dup_cursor
Fetch next from dup_cursor into @VUrnBAR,@VTxnUrnBarPat,@count

While (@@Fetch_Status = 0)
Begin
 Select @row_count=@count-1
 Set rowcount @row_count
 Delete from AdjustmentTransactions where UrnBAR=@VUrnBAR and TxnUrnBarPat=@VTxnUrnBarPat
Fetch next from dup_cursor into @VUrnBAR,@VTxnUrnBarPat,@count
End
Set rowcount 0
Close dup_cursor
Deallocate dup_cursor
If not Exists (select * from SYSINDEXES where name='PrimaryKey' and id=Object_id('AdjustmentTransactions'))
   CREATE  UNIQUE  INDEX [PrimaryKey] ON [dbo].[AdjustmentTransactions]([UrnBAR], [TxnUrnBarPat]) ON [PRIMARY]
Update Statistics AdjustmentTransactions

 

Thanks

Sandipan