• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3310
  • Last Modified:

How to automatically delete data from table in SQL Server before BCP is launched.


I'm using bcp.exe to insert data into SQL Server 2005 Express Edition. How do I get rid of the existing data in SQL Server table each time I run bcp.exe to insert new data.

The process must be automated. For this reason I can't run manually TRUNCATE TABLE table_name each time before the bcp is launched.

Thank you!
3 Solutions
you can do this using sql 2005 jobs,
first create a job on SQL 2005. please refer to this MSDN article Http://msdn2.microsoft.com/en-us/library/ms190268.aspx
on above job create a T-SQL step to truncate the table and create another step to run BCP.EXE
configure the truncate table step to run first and once that step is successful to run bcp.exe step
schedule the job.
for more information please refer http://msdn2.microsoft.com/en-us/library/ms187056.aspx 

ZaurbAuthor Commented:
Hi Chaturam,

The problem is that I'm using an SQL Server 2005 Express Edition. I can't find SQL Server Agent under the Object Explorer in Management Studio.

Is there another way to configure that?

Thank you!
ZaurbAuthor Commented:
OK, I think I resolved this issue by scheduling sqlcmd utility. It works, but...
any better solutions will be greatly appreciated. I'm sure there are much better and more effective approaches.

Thank you!
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
In TSQL(can be inserted in job step)....

use yourdb
if exists(select 1 from sysobjects where xtype in ('U') and name='yourtablename')
         drop table yourtable

Hope this helps...
ZaurbAuthor Commented:
But again, since I use EXPRESS Edition there's no SQL Server Agent for using Job Step...
Is there any external tool or some other method for that?

Thank you!
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
You may run the TSQL statement I gave you before running the bcp statement and by calling the osql command line...That way the statement for checking the existence of a specific table would be ran each time the bcp is ran...

Hope this helps
If you're using BCP, you'd want to use TRUNCATE instead of DROP, otherwise the BCP won't have a table to insert into.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now