Better way of handling many delete statements

Question

I have a long script for deleting some specific data from a lot (more than 100+) of tables and it looks something like this:

...

Delete from Table_name WHERE Company not in ('Company1','Company2')
Delete from Table_name WHERE Company not in ('Company1','Company2')
Delete from Table_name WHERE Company not in ('Company1','Company2')
Delete from Table_name WHERE Company not in ('Company1','Company2')

...

I want to change this so I don't have to change the variables on every line, I want to be able to set a where statement in the beginning, and that will change all the delete lines

declare .....something something
SELECT CompanyID
FROM _Company
WHERE  Company in ('Company1','Company2') -- I want to change this where statement only

Delete from Table_name WHERE Company not in (variable)
Delete from Table_name WHERE Company not in (variable)
Delete from Table_name WHERE Company not in (variable)
Delete from Table_name WHERE Company not in (variable)

Keep in mind that SQL Server will do a **lock escalation** if a single transaction tries to delete more than 5000 rows from any given table. This would put an **exclusive lock** on that table, preventing even `SELECT` from that table, until the transaction is committed (or rolled back). Try to avoid deleting more than 5000 rows at a time — marc_s, May 19 '17 at 08:38

score 3 · Accepted Answer · answered May 19 '17 at 07:48

You want a table variable. Alter column names, datatypes as necessary:

DECLARE @idsToKeep TABLE ( CompanyID int );

INSERT @idsToKeep
SELECT CompanyID
FROM _Company
WHERE Company IN ('Company1','Company2'); -- change this one place

DELETE Table_name1 WHERE CompanyID NOT IN ( SELECT CompanyID FROM @idsToKeep ); 
DELETE Table_name2 WHERE CompanyID NOT IN ( SELECT CompanyID FROM @idsToKeep ); 
DELETE Table_name3 WHERE CompanyID NOT IN ( SELECT CompanyID FROM @idsToKeep ); 
-- etc

Thanks a lot, that saved me a lot of time :) – Rubi May 22 '17 at 10:28 — Rubi, May 22 '17 at 10:28

score 0 · Answer 2 · answered May 19 '17 at 08:47

0

Another solution would be to create a cursor over the select statement and for each resulting entity run the delete statements with proper variables.

answered May 19 '17 at 08:47

Dave

349
1
15

Cursors shouldn't be recommended if there is another way. http://stackoverflow.com/questions/287445/why-do-people-hate-sql-cursors-so-much – Josh B May 19 '17 at 12:30
I didn't say it was recommended approach. I said "another solution would be", which means that it's an option. – Dave May 20 '17 at 19:56

Better way of handling many delete statements

2 Answers2