question

suraj avatar image
suraj asked

BCP utility of sql server

How does bcp utility of sql server work?

I run the bcp in command line, it will export/import the 1000 record in the batch I want to know, I want to know what method does it used to pass 1000 records at a time.

t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

You can use -b switch to control the size of the batch and -L switch to control the last number of the row to import/export. If you leave the -L then bcp will take all records.

Quote from Books online

-L last_row Specifies the number of the last row to export from a table or import from a data file. This parameter requires a value greater than (>) 0 but less than (<) or equal to (=) the number of the last row. In the absence of this parameter, the default is the last row of the file.

last_row can be a positive integer with a value up to 2^63-1.

-b batch_size Specifies the number of rows per batch of imported data. Each batch is imported and logged as a separate transaction that imports the whole batch before being committed. By default, all the rows in the data file are imported as one batch. To distribute the rows among multiple batches, specify a batch_size that is smaller than the number of rows in the data file. If the transaction for any batch fails, only insertions from the current batch are rolled back. Batches already imported by committed transactions are unaffected by a later failure.

Do not use this option in conjunction with the h"ROWS_PER_BATCH = bb" option.

For more information, see Managing Batches for Bulk Import.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Laerte Junior avatar image
Laerte Junior answered

From the SQL Server 7.0, bcp uses ODBC bulk copy API It enables you to create programs that use this API because can use with ODBC, OLE DB, SQL-DMO, and DB-Library based applications. From BOL.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.