Bulk Delete Many SQL Rows in Loop
If you have a very large delete to process the simplest way to delete it is to use the standard
DELETE FROM X WHERE Y...
However, if X has a LOT of referenced foreign keys, or is a very large table, and has many other concurrent users (live production environment), you would not want to execute this query, which may take several minutes to run and lock all other users from the table.
The solution is to delete the rows in small batches at a time. There are several ways to do this, but this is the simplest I have found so far:
DeleteAgain:
DELETE FROM X WHERE Y...
IF @@ROWCOUNT > 0
GOTO DeleteAgain
Created By: amos 1/30/2015 11:03:24 AM
|
|