Delete many (multiple) records in bunch: SQL-Server 2005

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.

USE adventureworks

GO

-- 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

GO

--Wants to delete rocords based on where condition of

--following query....

-- 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

AS

SET NOCOUNT ON

--INFINITE loop to iterate until and unless it will finish deleting records

WHILE(1=1)

BEGIN

DELETE TOP (@NumOfRow) FROM TempPurchaseOrderDetail WHERE PurchaseOrderID<3000

--LOOP will not break until we will reach to @@ROWCOUND=0

IF @@ROWCOUNT=0

BEGIN

PRINT('Records have been delete!!!!')

BREAK

END

END

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