BCP Export Limitation of 1000 rows

Question

BCP Export Limitation of 1000 rows

chandan_jha18

SSChampion

Points: 11330
More actions
November 23, 2015 at 5:05 am

#305485

Hello Everybody,
I am trying to export a 70 GB table to cloud and used Azure Migration tool to export the data(only data , no keys or indexes).
But it takes 10+ hours only to bcp-out the data(it uses bcp behind the scenes). I digged little bit about bcp and found that the bcp out has a limitation of exporting 1000 rows to a file no matter what batch size you provide to it.
I am looking at running this bcp command in multiple threads(just like SQL Server Snapshot agent does).
I also tried to find workarounds by dividing the table into chunks of 1 million rows and then query the table. For example:
select * from table where id between 1 and 1000000
select * from table where id between 1000001 and 2000000 and so on.
And then ran all these commands with bcp query out option in multiple windows. I got amazing results.
But then again, for a large number of tables I need to have a generic solution. Please let me know if my question and findings are clear and advise on the next move.
Thanks
Chandan Jha

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 1

Using SQL generate a rowset containing all needed bcp commands and save it to a file.

Create vbscript kind of https://gallery.technet.microsoft.com/scriptcenter/32e0146a-83fc-4ee1-af7b-52a57d57466c to run commands from file in parallel using WScript.Shell's Exec method.

Same can be achieved with powershell i suppose.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 2

serg-52 (11/23/2015)
Using SQL generate a rowset containing all needed bcp commands and save it to a file.
Create vbscript kind of https://gallery.technet.microsoft.com/scriptcenter/32e0146a-83fc-4ee1-af7b-52a57d57466c to run commands from file in parallel using WScript.Shell's Exec method.
Same can be achieved with powershell i suppose.

Hey thanks, I am looking at the VB code they provided, however it might be suitable for multiple tables.

Lets' say if you have a single table of 100 GB, a normal bcp would be churning out very low number of rowsand exporting it may take very long. I tried using single bcp and it took 24 +hours for my table. I realized by looking at my SQL Server activity monitor that the bcp was not causing high resource utilization and windows resource monitor proved it too. The Database I\O never exceeded 4-6 MB\sec.

So I am looking to reproduce the magic that SQL Server snapshot agent does while configuring replication and taking the snapshot, and boy, that is fast!!