How to create a bulk copy format file (ODBC)

To create a bulk copy 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_OUT to the file from the table or view.
5. Call bcp_columns to set the number of columns.
6. Call bcp_colfmt for each column to define its characteristics in the data file.
7. Call bcp_writefmt to create a format file describing the data file to be created by the bulk copy operation.
8. Call bcp_exec to execute the bulk copy operation.

A bulk copy operation run in this way creates both a data file containing the bulk copied data and a format file describing the layout of the data file.

Examples

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

// Sample showing ODBC BCP_OUT creating a format file.

//

// Assumes server has:

// CREATE TABLE BCPDate (cola int, colb datetime)

#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 bulk copy 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_OUT);

    // Set the number of output columns.

    retcode = bcp_columns(hdbc1, 2);

    // Describe the format of column 1 in the data file.

    retcode = bcp_colfmt(hdbc1, 1, SQLCHARACTER, -1, 5, NULL, 0, 1);

    // Describe the format of column 2 in the data file.

    retcode = bcp_colfmt(hdbc1, 2, SQLCHARACTER, -1, 20, NULL, 0, 2);

    // Create the format file.

    retcode = bcp_writefmt(hdbc1, "c:\\BCPFMT.fmt");

    // Execute the bulk copy.

    retcode = bcp_exec(hdbc1, &cRows);

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

    /* Clean up. */

    SQLDisconnect(hdbc1);

    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

    SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return(0);

}

To bulk copy this data file back into the database, see How to bulk copy by using a format file. To use this data file as the input to the bcp utility, enter at a command prompt:

bcp pubs..BCPDate in C:\Bcpodbc.bcp /fc:\Bcpfmt.fmt /SMyServer
/Usa /PMyPassWord

See Also