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

bcp_colfmt

bcp_writefmt

bcp_columns

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

bcp_exec

Using Data Files and Format Files

bcp_init