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

Is it SQL Server 2000 or 2k5 ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

it's sql server 2000

Thanks

sandipan