Initializing a Table in DTS

[Lftsk]

Hello,

I have a table whose contents are deleted and the table is then re-populated once a week via DTS. I believe there is a property (or some other way) in DTS that will automatically delete the contents of the table before
re-populating other than an SQL task of "DELETE FROM tblXXX". But I can't recall it. If anybody knows a way I would greatly appreciate it.

BTW I cannot express how much everybody here has helped me!

 

[TerryStover]

It's not a good idea. The dts trick drops and recreates the the target object. You lose any sequence numbers, constraints, privileges etc. Probably throws a lock on the system tables that could totally screw up other queries while it's running. Not sure what it will do to replication or incremental backups. I always put a truncate table followed by the dts copy, truncate isn't a logged operation. You have to be sa, dbo or table owner to do the truncate.

 

[Lftsk]

Thanks. I probably won't do it if that is the case. But if I do. What is the trick? Where do I go within DTS to truncate the table?

 

[tlbroadbent]

You open the DTS package and manually change the delete statements to truncate table in the Execute SQL task. There is no option that you can set that I know about. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm