troubleshooting Question

SQL Query Non-Cursor Delete records in one table from values in another table

Avatar of rwheeler23
rwheeler23Flag for United States of America asked on
Microsoft SQL Server 2005SQL
12 Comments4 Solutions404 ViewsLast Modified:
I have been charged with the task of cleaning up the customer table to remove those customers who have had no activity in the last 5 years. I have populated a table with a list of the customer numbers and now want a routine that will purge these customers from the database. There are about 30 tables that may have the customer number in them and I want to remove each customer from all tables. My thought is to create a stored procedure that will delete all records from all tables. My question is that I would prefer not to use a cursor to do this as illustrated in the code provided.In the sp I want the delete statements read like this:
delete table1 where custnmbr = '100', etc not delete table1 where custnmbr in (select custnmbr from customers_to_delete). Is it more efficient to delete one customer from all the tables at once or to delete all customers from each table as you go along? If the most efficient way is the latter of the two, then I do not need to worry about extracting the customer number first, but if the first way is more efficient how would I construct a sql script without using a cursor?
Declare @CUSTNMBR as char(50)

Declare CustomerCursor CURSOR FOR
select custnmbr from Customers_to_Delete

OPEN CustomerCursor
FETCH NEXT FROM CustomerCursor
INTO @CUSTNMBR

WHILE @@FETCH_STATUS = 0
BEGIN

	EXEC sp_delete_customer @CUSTNMBR
	FETCH NEXT FROM CustomerCursor
	INTO @CUSTNMBR
END

CLOSE CustomerCursor
DEALLOCATE CustomerCursor
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 4 Answers and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros