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

Saturday, March 7, 2009 |

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


0 comments: