Delete many (multiple) records in bunch: SQL-Server 2005
I had one task few months back. I was asked to delete 3 million records from one table containing total of 8 million records. You may think, ohh delete….. simple, execute delete statement with condition and sit back on your chair with relax. J Its not that simple, If I would have done it, I would have lost my database due to many reasons. I don’t want to go in details about those reasons. Let us focus on our main topic.
— creating one temporary table from AdventureWork database
— to perform delete operation.
SELECT * into AdventureWorks.DBO.TempPurchaseOrderDetail FROM Purchasing.PurchaseOrderDetail
–Above query has created 8788 row in my temp table
–Wants to delete rocords based on where condition of
— it has 6624 RECORDS in my table
SELECT * FROM TempPurchaseOrderDetail WHERE PurchaseOrderID<3000
Now, we will create one stored procedure which will delete all records from my table which will meet specific conditions.
CREATE PROC DeleteInBunch
@NumOfRow INT –number given for this variable, will be used with TOP in DELETE statement
SET NOCOUNT ON
–INFINITE loop to iterate until and unless it will finish deleting records
DELETE TOP (@NumOfRow) FROM TempPurchaseOrderDetail WHERE PurchaseOrderID<3000
–LOOP will not break until we will reach to @@ROWCOUND=0
PRINT(‘Records have been delete!!!!’)
So, finally we have created stored procedure; now let us see whether it actually works!!!
–SP will delete all records for specific condition in the bunch of 100
EXEC DeleteInBunch 100
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah