Possible TRUNCATE / BCP into a table in one TRANSACTION?

[Jonas Dahlqvist]

Hi all,

Is it possible to TRUNCATE a table and BCP data into the same table in one
TRANSACTION?
My problem is that I want to refresh(delete and via BCP append new data) a
table without disturbing running applications. Can I run BCP from a
SQL-script or a stored procedure?

Thank`s
Jonas Dahlqvist
Alfa Laval Automation AB

[Don Romano]

Truncating a table in a transaction doesn`t really seem to make sense because it is not a logged event (like `delete from table`) so you can`t rollback. Also, it is recommended that you do a full database dump after truncating a table since the truncate is a nonlogged operation.

You can investigate using the xp_cmdshell extended stored procedure to run a command string as an operating-system command shell. You could create a bat file to contain the bcp script or probably just include the bcp syntax in the xp_cmdshell call.

If you want the `refresh` to occur within a transaction I think you may have to create another table (or use a #temptable?) with the same structure and:

xp_cmdshell("bcp...") /* load refresh_table with data from bcp*/
Begin Transaction
delete from target_table
insert into target_table select * from refresh_table
Commit Transaction


Don

On 8/12/98 11:47:18 AM, Jonas Dahlqvist wrote:
> Hi all,

Is it possible to TRUNCATE a table and BCP data into the same
> table in one
TRANSACTION?
My problem is that I want to refresh(delete and
> via BCP append new data) a
table without disturbing running applications.
> Can I run BCP from a
SQL-script or a stored procedure?

Thank`s
Jonas
> Dahlqvist
Alfa Laval Automation AB