• Article

How to: Bulk Copy a SELECT Result Set (ODBC)

A complete sample shows how to use bulk copy functions to bulk copy out the result set of a SELECT statement.The complete sample code is in the file BulkCopySelectResult.cpp, which you can download from the SQL Server Downloads page on MSDN. This sample was developed using Microsoft Visual C++ 2005 and was developed for ODBC version 3.0 or later.

Security noteSecurity 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 crypto API.

To bulk copy out the result set of a SELECT statement

  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:

    • Specify NULL for the szTable parameter.

    • The name of the data file that receives result set data.

    • The name of a 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_OUT.

  5. Call bcp_control, set eOption to BCPHINTS and place in iValue a pointer to a SQLTCHAR array containing the SELECT statement.

  6. Call bcp_exec to execute the bulk copy operation.

When using these steps the file is created in native format. You can convert the data values to other data types by using bcp_colfmt. For more information, see How to: Create a Bulk Copy Format File (ODBC).