I was hoping somebody might be able to help me out here or point me in the direction of some resources that could shed some light.
I am using BCP utility to generate a large CSV file (~200 million lines) from SQL server.
I was wondering if it is possible to have BCP split the file (say every 10 million lines) while processing so that instead of having one large file I end up with 20 smaller ones?
I know I could split the file after I create it, but this takes up quite a bit more storage space as I would duplicate the original file.
Any help would be greatly appreciated!
Thanks very much in advance.
Mick
Here's an option for you. Use a while loop that will iterate through dynamic sql to generate the bcp code that is executed by xp_cmdshell. You should be able to take my code below and change it to your liking.
As an example, I included a print out of the bcp code generated by the SQL code below. Hope this is a viable solution for you.
declare @c int = 1 --file version
declare @s int = 1 --predicate begin int
declare @e int = 10000000 --predicate end int
while @s <= 200000000
begin
declare @sql varchar(8000)
print @sql
select @sql =
'bcp select * from [your_table] where <your_id> >= ' + convert(varchar(10),@s) + ' and [your_id] <=' + convert(varchar(10),@e) + ' out [your_file_name]' + convert(varchar(10),@c) + '.txt -c -t, -T -S' + ' [your_directory]'
exec master..xp_cmdshell @sql
set @s = @e + 1
set @e = @s + 9999999
set @c = @c + 1
end
Print out of @sql executed by exec master..xp_cmdshell @sql
bcp select * from [your_table] where <your_id> >= 1 and [your_id] <=10000000 out [your_file_name]1.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 10000001 and [your_id] <=20000000 out [your_file_name]2.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 20000001 and [your_id] <=30000000 out [your_file_name]3.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 30000001 and [your_id] <=40000000 out [your_file_name]4.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 40000001 and [your_id] <=50000000 out [your_file_name]5.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 50000001 and [your_id] <=60000000 out [your_file_name]6.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 60000001 and [your_id] <=70000000 out [your_file_name]7.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 70000001 and [your_id] <=80000000 out [your_file_name]8.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 80000001 and [your_id] <=90000000 out [your_file_name]9.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 90000001 and [your_id] <=100000000 out [your_file_name]10.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 100000001 and [your_id] <=110000000 out [your_file_name]11.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 110000001 and [your_id] <=120000000 out [your_file_name]12.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 120000001 and [your_id] <=130000000 out [your_file_name]13.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 130000001 and [your_id] <=140000000 out [your_file_name]14.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 140000001 and [your_id] <=150000000 out [your_file_name]15.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 150000001 and [your_id] <=160000000 out [your_file_name]16.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 160000001 and [your_id] <=170000000 out [your_file_name]17.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 170000001 and [your_id] <=180000000 out [your_file_name]18.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 180000001 and [your_id] <=190000000 out [your_file_name]19.txt -c -t, -T -S [your_directory]
bcp select * from [your_table] where <your_id> >= 190000001 and [your_id] <=200000000 out [your_file_name]20.txt -c -t, -T -S [your_directory]