Looping Through Deletes on Multiple Tables

Not long a go I got a request to bring one of the databases from the production environment to the test environment. This database is 500GB; not huge but still too big to fit in our test environment. So it was necessary to delete the data we didn’t need for testing. There were multiple tables I needed to delete from, and  I just wanted something to run that I could forget about and comeback to rather than having to run a delete for each table. So I made up the script below. You can change the size of the batch as I found that 1000000 gave the best performance (FYI: deleting  fewer than 5000 rows per loop removes the need for a table lock… anything greater locks the table.)

-- 2 different delete statements:
-- the rowcount method is deprecated in future versions.
-- the alternative is to use  "SELECT TOP" used in the CTE

-- Both still work the same way; if the @@rowcount (returns the number of rows affected by the last statement) is 0 then there are no more rows to delete
-- we loop through as there are billions of rows to delete, so if script fails for whatever reason we only lose last run

SET NOCOUNT ON

--variables which we are deleting by
DECLARE @ProductId nvarchar (32)
DECLARE @SalesId int

--store the name of the table we are going to delete
DECLARE @tblname NVARCHAR (128)

--we require different delete statements for each schema
--in our database. This works quite nicely as we don't have to get
--clever with deciding which delete statement to run
DECLARE @schema nvarchar (32)

--counting through total number of clients in the tables
--could use a cursor here but either way is ok
DECLARE @count INT

--@sql variable to execute by
DECLARE @sql nvarchar (MAX)

-- checking if temp table exists form previous run and dropping if it does
IF EXISTS (SELECT  * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = object_id(N'tempdb..#tablesToDelete'))
DROP TABLE #tablesToDelete;

-- put list of tabels you want to delete from here
CREATE TABLE #tablesToDelete ([TblName] nvarchar (128))
INSERT INTO #tablesToDelete VALUES('Sales.SalesNumbers')
INSERT INTO #tablesToDelete VALUES('Products.Products')
INSERT INTO #tablesToDelete VALUES('Bob.NoSchema')

SELECT @count = COUNT (*) from #tablesToDelete
while @count > 0
BEGIN

SELECT TOP 1 @tblname = [TblName] from #tablesToDelete

SET @schema = SUBSTRING (@tblName, 1, CAST (CHARINDEX ('.', @tblname) -1 AS INT))
SET @SalesId = 85
SET @ProductId = '2, 21, 9, 20'

IF @schema = 'Sales'
BEGIN
set @sql = '
USE MontyDev
DECLARE @continue INT
DECLARE @rowcount INT

SET @continue = 1
WHILE @continue = 1
BEGIN
PRINT GETDATE()
SET ROWCOUNT 1000000
delete from '+@tblname+' where tagId < '+CAST(@SalesId AS nvarchar)+'
SET @rowcount = @@rowcount
PRINT GETDATE()
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END'
EXEC (@sql)
END
ELSE IF @schema = 'Products'
BEGIN
set @sql = '
USE MontyDev
DECLARE @continue INT
DECLARE @rowcount INT

SET @continue = 1
WHILE @continue = 1
BEGIN
PRINT GETDATE()
;WITH CTE AS
(
select top 1000000 * from '+@tblname+' where clientId NOT in ( '+CAST(@ProductId AS nvarchar)+')
and tagId < '+CAST(@SalesId AS nvarchar)+
'
order by
[ProductId] ASC,
[SalesId] ASC

)
DELETE FROM CTE
SET @rowcount = @@rowcount
PRINT GETDATE()
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END

'
PRINT @tblname
EXEC (@SQL)
END
ELSE PRINT 'Delete query not defined for schema '+@schema+'!'
DELETE FROM #tablesToDelete where [TblName] = @tblname
SET @count = @count - 1
END

SET NOCOUNT OFF

Any questions/observations/criticisms always welcome via the comments or Twitter.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s