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);

    // Let ODBC know 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