[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Can I get a DTS package to run in a loop until a table is empty?

Posted on 2005-04-07
Medium Priority
Last Modified: 2008-02-01
Hello Experts,

I've been weening myself off Access though it is an easy tool to create stuff like this in.

I'd like to do this with a scheduled DTS package, here is how it is executing at the moment:

3 queries execute one after another-
1. Drop table DeleteDups
2. Make table query creates a table of ID numbers I would like to delete from another table
3. Delete query runs and deletes those ID numbers

I would like this to loop until the DeleteDups table has 0 rows (no more IDs to delete).

DeleteDups has only one column: MaxOfID

What would be the best way to do this?

- Mike
Question by:sph3rion
LVL 13

Expert Comment

ID: 13729146
not sure i understand, but are you deleting a record at a time??
 execsql task : drop table deletedups
 execsql task  create your DeleteDups table and fill with the dups to delete
 execslq task : call a stored procedure
In the stored procedure

Delete SomeTable
from   sometable  s
join     DeleteDups  d ON d.MaxOfID = s.Id


Accepted Solution

sudheeshthegreat earned 2000 total points
ID: 13729181
write a stored proc that executes these statements in a loop.

basic algorithm:
while RowCounter <> 0
  execute step 1
  execute step 2
  execute step 3
  RowCounter = rows in DeleteDups table.
end loop

you can then schedule this stored proc in DTS.

Author Comment

ID: 13731068
I am not deleting a record at a time and @@rowcount <> 0 did the trick

Kinda new at stored procs :)


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question