Quick and Simple Deletes

Day 6 and a Friday so keep it simple. There are occasions where you want to delete a huge table and truncation is just not an option for various reasons, but deleting from the table creates a huge transaction log. You don’t want to over engineer the solution so heres a simple example thats takes copy of the table and deleted in chunks of 1000 in this case, but you can use what ever suits .

--DELETES 
USE AdventureWorks
IF EXISTS ( SELECT 1 FROM sysobjects 
WHERE name ='SalesOrderDetailCopy') 
DROP TABLE SalesOrderDetailCopy
SELECT * INTO SalesOrderDetailCopy 
FROM Sales.SalesOrderDetail
WHILE 1=1
BEGIN
	DELETE TOP(1000)
	FROM SalesOrderDetailCopy  
	IF @@ROWCOUNT < 1000 BREAK
END 
SELECT COUNT(*) 
FROM SalesOrderDetailCopy