Extend "bcp queryout" row limit from 1000 rows per fetch.

Question

Is it possible to extend "bcp queryout" row fetch limit from its default of 1000 rows per fetch?

The requirement is to export a table data using a query with filters into a csv file, command is running fine but its fetching 1000 rows on each fetch and taking more time then expected, can Microsoft support suggest how to increase fetch limit from 1000 rows?

Example:

bcp <..qry..> queryout <..file location..> -c -S <...server....> -U <...uid...> -P <--pwd..>

1000 rows successfully bulk-copied to host-file. Total received: 1000

1000 rows successfully bulk-copied to host-file. Total received: 2000

1000 rows successfully bulk-copied to host-file. Total received: 3000

1000 rows successfully bulk-copied to host-file. Total received: 4000....

Answer 1

Hi @Prem Acharya BCP export has a limitation of exporting 1000 rows. Specify the batch size only works for import data.Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

BCP accepts an option -b to control the batch size. However, it only has effect when you load data, not when you export. More precisely, -b controls how often data is committed.

On output, BCP simply prints a progress message for every 1000 rows. If the speed decreases as the query runs, this is more likely to reflect the performance of the query itself. It's difficult to explain without seeing the query, but take a simple example. Say that you query is

SELECT * FROM VeryBigTable WHERE UserName = 'Fred'

There is no index here, so the table is scanned. Say also that the table is indexed on a monotonically growing id. In the beginning, when there were not many users, Fred was the user on many rows, so in the beginning of the scan, BCP finds many rows. However, as the business expanded, more users started to added rows. Also, Fred moved to a different role, so now he is only creating a row every now and then. Therefore, it will seem as the export is slowing down. But that is only because it is not finding as many rows as it did in the beginning.

This was just an example, and the explanation for your case may be different.