Bulk Copy by Using a Format File (ODBC)

Article
04/01/2012

This sample shows how to use the ODBC bcp_init function with a format file.

To bulk copy by using a format file

Allocate an environment handle and a connection handle.
Set SQL_COPT_SS_BCP and SQL_BCP_ON to enable bulk copy operations.
Connect to Microsoft® SQL Server™.
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.
Call bcp_readfmt to read the format file describing the data file to be used by the bulk copy operation.
Call bcp_exec to execute the bulk copy operation.

Example

This sample is not supported on IA64.

You will need an ODBC data source called AdventureWorks, whose default database is the AdventureWorks sample database. (You can download the AdventureWorks sample database from the Microsoft SQL Server Samples and Community Projects home page.) This data source must be based on the ODBC driver that is supplied by the operating system (the driver name is "SQL Server"). If you will build and run this sample as a 32-bit application on a 64-bit operating system, you must create the ODBC data source with the ODBC Administrator in %windir%\SysWOW64\odbcad32.exe.

This sample connects to your computer's default SQL Server instance. To connect to a named instance, change the definition of the ODBC data source to specify the instance using the following format: server\namedinstance. By default, SQL Server Express installs to a named instance.

Execute the first (Transact-SQL) code listing to create the table that the sample will use.

Copy the second code listing and paste it into a file called Bcpfmt.fmt. Each column in the table is separated by a tab character.

Copy the third code listing and paste it into a file called Bcpodbc.bcp. A tab character precedes each carriage return in the file.

Compile the fourth (C++) code listing with odbc32.lib and odbcbcp.lib. If you built with MSBuild.exe, copy Bcpfmt.fmt and Bcpodbc.bcp from the project directory into the directory with the .exe and then invoke the .exe.

Execute the fifth (Transact-SQL) code listing to delete the table that the sample used.

use AdventureWorks
CREATE TABLE BCPDate (cola int, colb datetime)

8.0
2
1SQLCHAR04"\t"1colaSQL_Latin1_General_Cp437_Bin
2SQLCHAR08"\r\n"2colbSQL_Latin1_General_Cp437_Bin

1
2

// compile with: odbc32.lib odbcbcp.lib
#include <stdio.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>

SQLHENV henv = SQL_NULL_HENV;
HDBC hdbc1 = SQL_NULL_HDBC; 

void Cleanup() {
   if (hdbc1 != SQL_NULL_HDBC) {
      SQLDisconnect(hdbc1);
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   }

   if (henv != SQL_NULL_HENV)
      SQLFreeHandle(SQL_HANDLE_ENV, henv);
}

int main() {
   RETCODE retcode;
   SDWORD cRows;

   // Allocate the ODBC environment and save handle.
   retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLAllocHandle(Env) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Notify ODBC that this is an ODBC 3.0 app.
   retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLSetEnvAttr(ODBC version) Failed\n\n");
      Cleanup();
      return(9);    
   }

   // Allocate ODBC connection handle, set BCP mode, and connect.
   retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLAllocHandle(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

   retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void *)SQL_BCP_ON, SQL_IS_INTEGER);
   if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
      printf("SQLSetConnectAttr(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Sample uses Integrated Security. Create SQL Server DSN using Windows NT authentication.
   retcode = SQLConnect(hdbc1, (UCHAR*)"AdventureWorks", SQL_NTS, (UCHAR*)"", SQL_NTS, (UCHAR*)"", SQL_NTS);
   if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
      printf("SQLConnect() Failed\n\n");
      Cleanup();
      return(9);
   }

   // Initialize the bulk copy.
   retcode = bcp_init(hdbc1, "BCPDate", "BCPODBC.bcp", NULL, DB_IN);
   if ( (retcode != SUCCEED) ) {
      printf("bcp_init(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Read the format file.
   retcode = bcp_readfmt(hdbc1, "BCPFMT.fmt");
   if ( (retcode != SUCCEED) ) {
      printf("bcp_readfmt(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

   // Execute the bulk copy.
   retcode = bcp_exec(hdbc1, &cRows);
   if ( (retcode != SUCCEED) ) {
      printf("bcp_exec(hdbc1) Failed\n\n");
      Cleanup();
      return(9);
   }

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

   // Cleanup
   SQLDisconnect(hdbc1);
   SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
   SQLFreeHandle(SQL_HANDLE_ENV, henv);
}

use AdventureWorks
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'BCPDate')
     DROP TABLE BCPDate
GO

Share via

Bulk Copy by Using a Format File (ODBC)

To bulk copy by using a format file

Example

See Also

Concepts

Other Resources

Additional resources