executing multiple bcp commands

[kizziebutler]

Is it possible to execute multiple bcp commands after one has just completed.

 

[jabrony76]

How are you executing them? If you are executing them via T-SQL via xp_cmdshell, you would just run them in the same statement:

declare @sql varchar(1000)

--set first bcp
set @sql = 'bcp "SELECT * FROM Table" queryout "\\server\folder\filename.txt" -c -q -S"ServerName" -T' 

exec master..xp_cmdshell @sql

--set second bcp
set @sql = 'bcp "SELECT * FROM Table2" queryout "\\server\folder\filename.txt" -c -q -S"ServerName" -T' 

exec master..xp_cmdshell @sql

If you are executing them in a cmd window, you can set up a .bat file that will run them.

Hope this helps,
Andy

 

[kizziebutler]

Thank you for your comments, I have 74 views created. Each bcp execute the views individually as the views are complicated table joins. Some of the views contain 90 million of records. I have created the view first and then execute the view using the bcp command. Is it possible, ie., above when one bcp is finished it will then gracefully go onto the next one, as each bcp command fills the tempdb very quickly. Thank you for your comments.

 

[fredericofonseca]

I would consider using a DTS package for this.

You can set it up so that tasks are only executed on sucessefull (or not) completion of a previous task(s).



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[mrdenny]

You can use some batch file programming to do this. Something like this will do the trick.

First setup a text file with the names of the views that you want to export the data from. Save the file in a folder and name the file "views.txt". Create a batch file (.bat) in the same folder. But this in the batch file.

@for /F %a IN (views.txt) DO BCP "%a" output "d:\outputpath\%a.txt" -S SERVERNAME –T –t "," –w

Be sure to change the path and servername.
From a command prompt run the batch file. It will drop you to a dos prompt when it's done.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com