1

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

IrishMickeyWard
  • 147
  • 2
  • 11
  • Possible duplicate? http://stackoverflow.com/questions/17632236/exporting-sql-server-table-to-multiple-part-files – Vlad Feinstein Nov 18 '15 at 15:50
  • Ah thank you Vlad. This provides insight, but this talks about chunking files by byte size. I was hoping somebody would have an idea on how to do this by line number. I understand if it may not be possible to do it with BCP. – IrishMickeyWard Nov 18 '15 at 16:12
  • What about this suggestion (from that same page)? `bcp "SELECT * FROM MyTable WHERE Id BETWEEN 0 AND 10000000" queryout … ` – Vlad Feinstein Nov 18 '15 at 16:48
  • Yes Vlad. Between that info and the info from Mike below I am on my way! You have also been most helpful. – IrishMickeyWard Nov 18 '15 at 17:51

1 Answers1

2

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]
Mike Zalansky
  • 796
  • 7
  • 14