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

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?

Thanks,
- Mike
LVL 1
sph3rionAsked:
Who is Participating?
 
sudheeshthegreatCommented:
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.
0
 
KarinLoosCommented:
not sure i understand, but are you deleting a record at a time??
why?
 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


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

Kinda new at stored procs :)

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.