Deleting million rows after checking duplication

I have a table(9 fields) which contains around 10 million records. Within these 10 million records, don't know how many are duplicated rows. I wrote a cursor which checks duplication on all of the 9 fields within each row, and then returns back with the count of rows that are duplicated. I subtact 1 row and delete all the other rows. Problem is that this thing takes a lot of time to execute. At the current rate(approx. 90 records/hour) this cursor is going to take months to clean the table.

There is no PK or no indexing what so ever on the table, and I HAVE to check each and every field for duplication(all except 1 fields are nvarchar).

Please help me. I need to sort this out

What about doing a grouping on the table and pulling hte data out to another table.

Something like:

Select cola,ColB,Colc (all columns here)
INTO SomeNewTable
FROM SomeTable
Group by cola,ColB,Colc (all columns here)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

delete rows by checking conditions in more than one column

Hi All,

I have tables in three hierarchy...
I need to delete rows from the table which in 3rd level of hierarchy.

For ex: Table 1 has primary key col1.
Table 2 has primary key Col2 and Foreign key COL1 and
Table 3 has primary key Col3 and Foregn key Col2.

Now I have to delete all the rows in table three where Table3.Col2=Table2.Col2
and Table2.Col1=Table1.Col1

I could insert successfully using this condition but I am unable to delete.
I tried nested queries but it fails as the sub query returns more than one row.

Please help me how to overcome this.
An example is more helpful.

Thank you
Madhavi

Quote:

Originally Posted by madhavi123

Hi All,

I have tables in three hierarchy...
I need to delete rows from the table which in 3rd level of hierarchy.

For ex: Table 1 has primary key col1.
Table 2 has primary key Col2 and Foreign key COL1 and
Table 3 has primary key Col3 and Foregn key Col2.

Now I have to delete all the rows in table three where Table3.Col2=Table2.Col2
and Table2.Col1=Table1.Col1

I could insert successfully using this condition but I am unable to delete.
I tried nested queries but it fails as the sub query returns more than one row.

Please help me how to overcome this.
An example is more helpful.

Thank you
Madhavi

Please don't post questions in the articles section.
Moved to the forum.