Unanswered: Use DTS to delete all records before import?
I want to modify a current DTS job I have. It simply copies records from one table to another after a given date. The catch is I only have read priviliges on the source table. I currently delete all the records from the destination table but have to use Query Analyzer to do it and then run the current package that is just a simple select statement. The statement in the DTS job is something like:
WHERE date_field > '2004-09-30'
I have been running this multiple times per month since every week or so I want to get more recent data to run a couple of reports. The source table is not ours, it is in a different department, so I can only read off of it. The destination server is ours so I have full priviliges there. I assume the fact that the source server allows me limited access that I cannot run a "delete from tablename" first even though it is on our table? Is there another way to set this up? Thanks again. You guys are the best.
Assuming that you are setting this up using the DTS Wizard, there is a check box (I think in the transformations) that allows you to delete the rows in the target table before the copy. If you are using the DTS Designer, just add a job step to delete all of the rows in the target table before you copy.
I am in Designer, sorry, I should have clarified. I would prefer to use Designer, as a matter of fact, if there is a way to do this and have it run overnight that would be great. What do you mean "add a job step"? I have tried to add a delete statement before the select * into statement but that didn't work.