How to delete duplicate from 140 GB SQL server table where 30% records are duplicate without causing overheads like transaction logs full?

All we need is an easy explanation of the problem, so here it is.

How to delete duplicate from 140 GB SQL server table where 30% records are duplicate without causing overheads like transaction logs full?

table doesn’t have primary key.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If you have no Primary Key then you have no Foreign Keys referencing it.
That leaves you in the privileged position of being able to remove the table and completely recreate it or, rather, create a new table with only the rows you want, then drop the original table and rename the new table, something like this.

create table t2 
( a ... 
, b ... 
, c ... 
) 
primary key ( a, b, c ); 

insert into t2 ( a, b, c ) 
select a, b, c 
from t1 
where ... 
; 

drop table t1 ; 

EXEC sp_rename 't2', 't1' ; 

Of course, how you identify the rows that you want to take across is up to you.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply