How to bulk copy without a format file (ODBC)

To bulk copy without 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 view or table, or DB_OUT to the file from the table or view.
5. Call bcp_exec to execute the bulk copy operation.

When DB_OUT is set with these steps, the file is created in native format. The file can then be bulk copied into a server by following these same steps, except that DB_OUT is set instead of DB_IN. This works only if both the source and target tables have exactly the same structure.

Examples

The following example shows using bulk copy functions to create a native mode data file. Most error-checking code was removed to simplify this example.

// Sample showing ODBC BCP_OUT in native mode format.
//

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

   // Bulk copy 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 
   // then 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..authors", "c:\\BCPODBC.bcp",
                  "c:\\BCPERROR.out, DB_OUT);
   // Note that the test is for the bulk copy return of SUCCEED,
   // not the ODBC return of SQL_SUCCESS.
   if ( (retcode != SUCCEED) )
   {
         ProcessLogMessages(SQL_HANDLE_DBC, hdbc1,
                     "bcp_init(hdbc1) Failed\n\n");
         return(9);
   }

   // Execute the bulk copy.
   retcode = bcp_exec(hdbc1, &cRows);
   if ( (retcode != SUCCEED) )
   {
         ProcessLogMessages(SQL_HANDLE_DBC, hdbc1,
                     "bcp_exec(hdbc1) Failed\n\n");
         return(9);
   }
   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);
}

The data file created by this sample is a native mode file. To bulk copy the data back into the table, recompile the application after changing the bcp_init call from BCP_OUT to BCP_IN. To use the file as native-mode input to the bcp utility, enter at a command prompt:

bcp MyDB..DateTable in c:\BCPODBC.bcp /n /SMyServer 
/Usa /PMyPassWord

See Also

bcp_exec

Using Data Files and Format Files

bcp_init