Share via

  • Article

How to: Bulk Copy Data from Program Variables (ODBC)

There is a sample showing how to use bulk copy functions to bulk copy data from program variables to SQL Server using bcp_bind and bcp_sendrow. (Error-checking code is removed to simplify this example.)

The complete sample code is in this file: BulkCopyFromVariables.cpp, which you can download from the SQL Server Code Samples page. This sample was developed using Microsoft Visual C++ 2005 and was developed for ODBC version 3.0 or later.

Security Note   When possible, use Windows Authentication. If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32 cryptoAPI.

To use bulk copy functions directly on program variables

  1. Allocate an environment handle and a connection handle.

  2. Set SQL_COPT_SS_BCP and SQL_BCP_ON to enable bulk copy operations.

  3. Connect to SQL Server.

  4. Call bcp_init to set the following information:

    • The name of the table or view to bulk copy from or to.

    • Specify NULL for the name of the data file.

    • The name of an data file to receive any bulk copy error messages (specify NULL if you do not want a message file).

    • The direction of the copy: DB_IN from the application to the view or table or DB_OUT to the application from the table or view.

  5. Call bcp_bind for each column in the bulk copy to bind the column to a program variable.

  6. Fill the program variables with data, and call bcp_sendrow to send a row of data.

  7. After several rows have been sent, call bcp_batch to checkpoint the rows already sent. It is good practice to call bcp_batch at least once per 1000 rows.

  8. After all rows have been sent, call bcp_done to complete the operation.

You can vary the location and length of program variables during a bulk copy operation by calling bcp_colptr and bcp_collen. Use bcp_control to set various bulk copy options. Use bcp_moretext to send text, ntext, and image data in segments to the server.