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

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

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