Performing bulk inserts with SQLBulkOperations()

You can insert new rows into a table or view at a data source with a call to SQLBulkOperations().

Before you begin

Before calling SQLBulkOperations(), an application must ensure that the required bulk operation is supported. To check for support, call SQLGetInfo() with an InfoType of SQL_DYNAMIC_CURSOR_ATTRIBUTES1 or SQL_DYNAMIC_CURSOR_ATTRIBUTES2. Check the following attributes to verify that support is available:
  • SQL_CA1_BULK_ADD
  • SQL_CA1_BULK_UPDATE_BY_BOOKMARK
  • SQL_CA1_BULK_DELETE_BY_BOOKMARK
  • SQL_CA1_BULK_FETCH_BY_BOOKMARK

About this task

SQLBulkOperations() operates on the current result set through a dynamic cursor, which allows you detect any changes that are made to the result set. SQLBulkOperations() inserts a row using data in the application buffers for each bound column.

Procedure

To perform a bulk insert:

  1. Execute a query that returns a result set.
  2. Set the SQL_ATTR_ROW_ARRAY_SIZE statement attribute to the number of rows that you want to insert.
  3. Call SQLBindCol() to bind the data that you want to insert.
    Bind the data to an array with a size that is equal to the value of SQL_ATTR_ROW_ARRAY_SIZE.
    One of the following conditions must be true:
    • The size of the row status array to which the SQL_ATTR_ROW_STATUS_PTR statement attribute points is equal to SQL_ATTR_ROW_ARRAY_SIZE.
    • SQL_ATTR_ROW_STATUS_PTR is a null pointer.

    All bound columns that have a data length of SQL_COLUMN_IGNORE, and all unbound columns must accept NULL values or have a default.

  4. Call SQLBulkOperations(StatementHandle, SQL_ADD) to perform the insertion.
    SQLBulkOperations() ignores the row operation array to which SQL_ATTR_ROW_OPERATION_PTR points.
  5. If the application has set the SQL_ATTR_ROW_STATUS_PTR statement attribute, inspect the row status array to see the result of the operation.

Example

The following example is an application that executes a query and uses SQLBulkOperations() to insert 10 rows of data into table CUSTOMER.
a#define ROWSET_SIZE 10
/* declare and initialize local variables        */
SQLCHAR sqlstmt[] =
  "SELECT Cust_Num, First_Name, Last_Name FROM CUSTOMER";
SQLINTEGER Cust_Num[ROWSET_SIZE];
SQLCHAR First_Name[ROWSET_SIZE][21];
SQLCHAR Last_Name[ROWSET_SIZE][21];
SQLINTEGER Cust_Num_L[ROWSET_SIZE];
SQLINTEGER First_Name_L[ROWSET_SIZE];
SQLINTEGER Last_Name_L[ROWSET_SIZE];
SQLUSMALLINT rowStatus[ROWSET_SIZE];
/* Set up dynamic cursor type */
rc = SQLSetStmtAttr(hstmt,
  SQL_ATTR_CURSOR_TYPE,
  (SQLPOINTER) SQL_CURSOR_DYNAMIC,
  0);
/* Set pointer to row status array               */
rc = SQLSetStmtAttr(hstmt,
  SQL_ATTR_ROW_STATUS_PTR,
  (SQLPOINTER) rowStatus,
  0);
/* Execute query */
rc = SQLExecDirect(hstmt,sqlstmt,SQL_NTS);
/* Call SQLBindCol() for each result set column  */
rc = SQLBindCol(hstmt,
  1,
  SQL_C_LONG,
  (SQLPOINTER) Cust_Num,
  (SQLINTEGER) sizeof(Cust_Num)/ROWSET_SIZE,
  Cust_Num_L);
rc = SQLBindCol(hstmt,
  2,
  SQL_C_CHAR,
  (SQLPOINTER) First_Name,
  (SQLINTEGER) sizeof(First_Name)/ROWSET_SIZE,
  First_Name_L);
rc = SQLBindCol(hstmt,
  3,
  SQL_C_CHAR,
  (SQLPOINTER) Last_Name,
  (SQLINTEGER) sizeof(Last_Name)/ROWSET_SIZE,
  Last_Name_L);
…
/* For each column, place the new data values in */
/* the rgbValue array, and set each length value */
/* in the pcbValue array to be the length of the */
/* corresponding value in the rgbValue array.    */
…
/* Set number of rows to insert                  */
rc = SQLSetStmtAttr(hstmt,
  SQL_ATTR_ROW_ARRAY_SIZE,
  (SQLPOINTER) ROWSET_SIZE,
  0);
/* Perform the bulk insert                       */
rc = SQLBulkOperations(hstmt, SQL_ADD);