exec xp_cmdshell 'bcp "select .. from ... where ..." queryout "C:\...\xxx0001.sql" -c -C RAW -T'
exec xp_cmdshell 'bcp "select .. from ... where ..." queryout "C:\...\xxx0002.sql" -c -C RAW -T'
.
.
.
exec xp_cmdshell 'bcp "select .. from ... where ..." queryout "C:\...\xxx0999.sql" -c -C RAW -T'

set nocount on

-- table of bcp commands to do
select
	bcpcmd = 'bcp "select t.datacol from tab t (nolock) where t.schema = ' + ltrim(number) + ' order by t.ordercol" queryout "C:\...\XXX' + ltrim(number) + '.sql" -c -C RAW -T'
into #bcp
from master.dbo.spt_values where type = 'p' and number / 100 = 1

declare @cmd varchar(8000)

-- loop through all the bcp table and bcp the code
declare csr_bcp cursor fast_forward for
	select bcpcmd from #bcp

open csr_bcp
fetch next from csr_bcp into @cmd
while @@fetch_status = 0
begin
	select @cmd as [bcpthis...]
	-- exec master..xp_cmdshell @cmd, no_output
	fetch next from csr_bcp into @cmd
end

close csr_bcp
deallocate csr_bcp

drop table #bcp

quote:

---

Originally posted by MichaelP

Parallel Execution might put lots of stress on your system, so take that into account when exploring that option. Running this thing at night might not be bad, but mid day might slow things down for users, even with nolock.

---

Event Class  Application Name  Connection ID   Start Time    End Time

Connect      SQLDMO_1              233        09:26:21.060
Disconnect   SQLDMO_1              233        09:26:21.060  09:26:27.467

Connect      Microsoft SQL Server  234        09:26:28.187
-- here multiple bcp.exe's connects/disconnects (in quite messy order)
Disconnect   Microsoft SQL Server  330        09:26:50.873  09:26:53.060

Test Results are in:
---------------------------------------------------------
DMO BulkCopy UseExistingConnection = True	2 sec	-
DMO BulkCopy UseExistingConnection = False	3 sec	-
Async BCP with Shell Command			5 sec	-
Sequential BCP with xp_cmdshell			17 sec	-
---------------------------------------------------------


Detailed Test results

DMO BulkCopy UseExisitngConnection = True
1,9375 sec
EventClass			Application	Start			Finish
---------------------------------------------------------------------------------------
Audit Login	SQLDMO_1	2005-01-31 19:21:56.727		
Audit Logout	SQLDMO_1	2005-01-31 19:21:56.727	2005-01-31 19:21:58.680


DMO BulkCopy UseExisitngConnection = False
3,234375 sec
EventClass			Application	Start			Finish
---------------------------------------------------------------------------------------
Audit Login	SQLDMO_1	2005-01-31 19:23:37.133		
...
Audit Logout	SQLDMO_101	2005-01-31 19:23:40.367	2005-01-31 19:23:40.383	
Audit Logout	SQLDMO_1	2005-01-31 19:23:37.133	2005-01-31 19:23:40.383	


BCP Shell Command
5,34375 sec
EventClass			Application	Start			Finish
---------------------------------------------------------------------------------------
Audit Logout	Microsoft SQL Server	2005-01-31 19:25:59.290	2005-01-31 19:25:59.320	
...
Audit Logout	Microsoft SQL Server	2005-01-31 19:26:05.337	2005-01-31 19:26:05.367	


xp_cmdshell loop with bcp
17.70 sec
EventClass			Application	Start			Finish
---------------------------------------------------------------------------------------
Audit Logout	Microsoft SQL Server	2005-01-31 19:32:50.117	2005-01-31 19:32:50.117
...
Audit Logout	Microsoft SQL Server	2005-01-31 19:33:07.630	2005-01-31 19:33:07.630	


The Test Code
(2 vbscript programs and a T-SQL loop as follows)
---------------------------------------------------------------------------------------
Dim srv,bcp,db,i,StartTime,var

var = MsgBox("UseExistingConnection",4)

Set srv = CreateObject("SQLDMO.SQLServer")
Set bcp = CreateObject("SQLDMO.BulkCopy")
Set db = CreateObject("SQLDMO.Database")
srv.EnableBcp = True
srv.LoginSecure = True
srv.Connect
Set db = srv.Databases("pubs")

if var = 6 then 'Yes clicked
	bcp.UseExistingConnection = True
else
	bcp.UseExistingConnection = False
end if

StartTime = Timer

For i = 1 To 100
	bcp.DataFilePath = "C:\temp\t" & i & ".txt"
	db.Tables("authors").ExportData bcp
Next

Set bcp = Nothing
Set db = Nothing
srv.DisConnect
Set srv = Nothing

if var = 6 then
	MsgBox Timer - StartTime & " seconds - bulk UseExistingConnection = True"
else
	MsgBox Timer - StartTime & " seconds - bulk UseExistingConnection = False"
end if
---------------------------------------------------------------------------------------
Dim StartTime
StartTime = Timer
For i = 1 To 100
CreateObject("WScript.Shell").Run "bcp pubs..authors out C:\temp\t" & i & ".txt -c -C RAW -T", 0
Next
MsgBox Timer - StartTime & " seconds - bcp shell"
---------------------------------------------------------------------------------------
set nocount on
declare @i int, @d datetime, @cmd nvarchar(256)
select @i = 1, @d = getdate()
while @i < 101
begin
	set @cmd = N'bcp pubs..authors out C:\temp\t' + ltrim(@i) + N'.txt -c -C RAW -T'
	exec master..xp_cmdshell @cmd, no_output
	set @i = @i+1
end
select datediff(millisecond,@d,getdate())/1000.0 as [seconds - xp_cmdshell loop]
---------------------------------------------------------------------------------------