Search This Blog

Saturday 17 September 2011

How to delete large amount of rows from table in SQL


In some scenario we have to delete large amount of rows from sql table and it will going to timeout if the table has very large amount of rows (Some tables in the database has more than crore rows). In this scenario we need to delete some small amount of records and from the table and continue the process until all records in the table deleted. 
Query for recursive deletion from the table in SQL  
Below codes delete 50000 rows recursively untill all records int the table deleted. So it never meets the timeout exception and will complete execution faster than normal query 
WHILE exists (
SELECT * FROM myTable WHERE name like ‘%ab%’
 )
DELETE TOP (50000) scanned_cont_dtls WHERE name like ‘%ab%’;

No comments:

Post a Comment