I have a procedure that call another procedure that makes a query and generate a csv file, and works fine, the problem is that there are a lot of contacts and I need to split in multiple files every 1000 contacts, is it possible to do this?
Thanks.
No, it is not possible to split a file using BCP. The BCP command only allows for a single destination file to be specified when using the "out" or "queryout" option.
To split up your single dataset into multiple files either split it up in SQL first and execute BCP for each piece.
Another option (assuming 1000 records is not a hard requirement, you just want to break the data up into smaller chunks) - keep the data in a single table and queryout the data with BCP as opposed to using the "out" option. To do this, you'll need a column in your data that you can use to split the data into chunks. Any numeric identifier field works great for this. Execute bcp with query out and add where clause to filter your rows down by splitting up the data using the numeric key. Like this:
"select * from db.dbo.your_table where right( cast( num_key ) as varchar( 12 ) ), 1 ) = '0'"
and
"select * from db.dbo.your_table where right( cast( num_key ) as varchar( 12 ) ), 1 ) = '1'"
and
"select * from db.dbo.your_table where right( cast( num_key ) as varchar( 12 ) ), 1 ) = '2'"
and.... so on and so on until you get all 0 through 9. This should split our data up nicely into 10 evenly sized chunks. If you need smaller chunks use 2 characters at the end of your numeric key and get 100 chunks. Lots of other ways to split data up in this manner.
But in the end, you are going to execute 10 bcp statements here as well. 1 bcp statement per file created.