How to bulk copy by using a format file (ODBC)

To bulk copy by using a format file

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 Microsoft® SQL Server™.
   
   
4. Call bcp_init to set the following information:
   • The name of the table or view to bulk copy from or to.
     
     
   • The name of the data file that contains the data to copy into the database or that receives data when copying from the database.
     
     
   • 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_IN from the file to the table or view.
5. Call bcp_readfmt to read the format file describing the data file to be used by the bulk copy operation.
   
   
6. Call bcp_exec to execute the bulk copy operation.

Examples

The following example shows using bulk copy functions with both an data file and format file. Error-checking code was removed to simplify this example.

// Sample showing ODBC BCP_IN using a format file.
//
// Assumes server has:
// CREATE TABLE BCPDate (cola int, colb datetime)
// Assumes you have the format file and datafile from the example 
// in How to create a bulk copy format file.

#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>

SQLHENV      henv = SQL_NULL_HENV;
HDBC         hdbc1 = SQL_NULL_HDBC;     

int main() {
   RETCODE      retcode;
   // BCP variables.
   SDWORD   cRows;

   // Allocate the ODBC environment and save handle.
   retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
   // Notify ODBC that this is an ODBC 3.0 app.
   retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                     (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
   // Allocate ODBC connection handle, set BCP mode, and connect.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
   retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP,
                        (void *)SQL_BCP_ON, SQL_IS_INTEGER);
   retcode = SQLConnect(hdbc1, "MyDSN", SQL_NTS,
                     "sa", SQL_NTS, "MyPassWord", SQL_NTS);
   
   // Initialize the bulk copy.
   retcode = bcp_init(hdbc1, "pubs..BCPDate", "c:\\BCPODBC.bcp",
                  NULL, DB_IN);

   // Read the format file.
   retcode = bcp_readfmt(hdbc1, "c:\\BCPFMT.fmt");

   // Execute the bulk copy.
   retcode = bcp_exec(hdbc1, &cRows);

   printf("Number of rows bulk copied in = %d.\n", cRows);

   /* Clean up. */
   SQLDisconnect(hdbc1);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);
   return(0);
}

See Also

bcp_exec

How to bulk copy by using a format file

bcp_init

Using Data Files and Format Files

bcp_readfmt