c++ 访问sqlserver数据库，插入速度优化

1. 概述

使用c++访问数据库的方法很多，由于我访问的是sqlserver数据库，于是上MSDN查了一下有哪些访问技术，主要有以下几种：

• ODBC  
• OLEDB
• ADO

ADO是OLEDB的封装，使用起来比OLEDB方便。由于ADO比OLEDB多了一层，其速度可能不及OLEDB，所以就不考虑ADO了。

数据库获取方法参考MSDN：https://msdn.microsoft.com/en-us/library/windows/desktop/aa968814(v=vs.85).aspx

ODBC访问sqlserver有一个好处，可以在linux上使用。linux上可以使用FreeTDS作为sqlserver的ODBC驱动。OLEDB则只能在windows上运行。

1.1 ODBC（Open Database Connectivity）

ODBC是微软弄出来的一个可以访问各种数据库的接口。ODBC存在时间比较长，是一个比较稳定的接口。它既然能够访问各种接口，所以它是一个最小公共集的接口。这个接口里面可能会缺少某些数据库的特定功能。

ODBC只是一个接口，想要使用ODBC必须提供驱动。 sqlserver 提供SQL Server Native Client ODBC driver来支持ODBC接口。

1.2 OLEDB

OLEDB是一组基于COM的接口。OLEDB提供统一的接口，访问各种形式的数据。说到OLEDB就必须提交两个概念：consumer（使用接口的程序）、provider（接口的实现者）。OLEDB是定义一组接口，每个数据库供应商如果要是自己的数据库能通过OLEDB访问，就必须提供OLEDB Provider。

sqlserver的的provider是The SQL Server Native Client OLE DB provider。

2. 使用ODBC插入数据

sqlserver使用ODBC的MSDN地址：https://msdn.microsoft.com/en-us/library/ms131415(v=sql.105).aspx

ODBC接口的介绍地址：https://msdn.microsoft.com/en-us/library/windows/desktop/ms710252(v=vs.85).aspx

ODBC插入数据速度快捷的方法主要有两种：

1. SQLBulkOperations 

#include <windows.h>#include <sqlext.h>#include <stdio.h>#include <time.h>SQLHENV henv = NULL;SQLHDBC hdbc = NULL;SQLHSTMT hstmt = NULL;SQLRETURN retcode;#define COUNT  (100000)#define ROW_ARRAY_SIZE 1000typedef struct{SQLINTEGER rec_num;SQLINTEGER rec_numInd;SQLCHAR date[9];SQLINTEGER dateInd;SQLCHAR time[9];SQLINTEGER timeInd;SQLCHAR reff[11];SQLINTEGER reffInd;SQLCHAR acc[11];SQLINTEGER accInd;SQLCHAR stock[7];SQLINTEGER stockInd;SQLCHAR bs[2];SQLINTEGER bsInd;SQLCHAR price[9];SQLINTEGER priceInd;SQLCHAR qty[9];SQLINTEGER qtyInd;SQLCHAR status[2];SQLINTEGER statusInd;SQLCHAR owflag[4];SQLINTEGER owflagInd;SQLCHAR ordrec[9];SQLINTEGER ordrecInd;SQLCHAR firmid[6];SQLINTEGER firmidInd;SQLCHAR branchid[6];SQLINTEGER branchidInd;SQLSCHAR checkord[16];SQLINTEGER checkordInd;} ORDWTH;typedef struct{SQLINTEGER id;SQLCHAR date[20];SQLCHAR abbr[10];//SQLINTEGER idInd;SQLINTEGER dateInd;SQLINTEGER abbrInd;} Test;Test test_array[ROW_ARRAY_SIZE];ORDWTH  ordwth_array[ROW_ARRAY_SIZE];int rec_num = 1;void main(){retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);retcode = SQLConnect(hdbc, (SQLCHAR*) "ctp2_lx", SQL_NTS, (SQLCHAR*)"sa", SQL_NTS, (SQLCHAR*)"123456", SQL_NTS);if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){}else{SQLCHAR msg[128];SQLCHAR state[128];SQLINTEGER error_id;SQLSMALLINT text;SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1, state, &error_id, msg, 128, &text);printf("db connect fail, sqlstate=%s, errormsg=%s\n", state, msg);system("pause");return;}retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);printf("Inserting...\n");time_t begin;time(&begin);//设定SQL_ATTR_ROW_ARRAY_SIZE属性，bulk的长度SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)ROW_ARRAY_SIZE, 0);SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER)sizeof(ORDWTH), 0);SQLUSMALLINT ParamStatusArray[ROW_ARRAY_SIZE] = { 0 };//设定状态数组retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, ParamStatusArray, 0);SQLINTEGER nBindOffset = 0;SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_OFFSET_PTR, (SQLPOINTER)&nBindOffset, 0);retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER)SQL_CONCUR_ROWVER, 0);retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, 0);//进行一次查询，得到result setretcode = SQLExecDirect(hstmt, (SQLCHAR*)"select rec_num, date, time, reff, acc, stock, bs, price, qty, status, owflag, ordrec, firmid, branchid, checkord  from ashare_ordwth", SQL_NTS);//retcode = SQLExecDirect(hstmt, (SQLCHAR*)"select id, date, abbr from test", SQL_NTS);retcode = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);/*SQLBindCol(hstmt, 1, SQL_C_SHORT, &test_array[0].id, 0, &test_array[0].idInd);SQLBindCol(hstmt, 2, SQL_C_CHAR, &test_array[0].date, 20, &test_array[0].dateInd);SQLBindCol(hstmt, 3, SQL_C_CHAR, &test_array[0].abbr, 10, &test_array[0].abbrInd);*/retcode = SQLBindCol(hstmt, 1, SQL_C_LONG, &ordwth_array[0].rec_num, 0, &ordwth_array[0].rec_numInd);SQLBindCol(hstmt, 2, SQL_C_CHAR, &ordwth_array[0].date, sizeof(ordwth_array[0].date), &ordwth_array[0].dateInd);SQLBindCol(hstmt, 3, SQL_C_CHAR, &ordwth_array[0].time, sizeof(ordwth_array[0].time), &ordwth_array[0].timeInd);SQLBindCol(hstmt, 4, SQL_C_CHAR, &ordwth_array[0].reff, sizeof(ordwth_array[0].reff), &ordwth_array[0].reffInd);SQLBindCol(hstmt, 5, SQL_C_CHAR, &ordwth_array[0].acc, sizeof(ordwth_array[0].acc), &ordwth_array[0].accInd);SQLBindCol(hstmt, 6, SQL_C_CHAR, &ordwth_array[0].stock, sizeof(ordwth_array[0].stock), &ordwth_array[0].stockInd);SQLBindCol(hstmt, 7, SQL_C_CHAR, &ordwth_array[0].bs, sizeof(ordwth_array[0].bs), &ordwth_array[0].bsInd);SQLBindCol(hstmt, 8, SQL_C_CHAR, &ordwth_array[0].price, sizeof(ordwth_array[0].price), &ordwth_array[0].priceInd);SQLBindCol(hstmt, 9, SQL_C_CHAR, &ordwth_array[0].qty, sizeof(ordwth_array[0].qty), &ordwth_array[0].qtyInd);SQLBindCol(hstmt, 10, SQL_C_CHAR, &ordwth_array[0].status, sizeof(ordwth_array[0].status), &ordwth_array[0].statusInd);SQLBindCol(hstmt, 11, SQL_C_CHAR, &ordwth_array[0].owflag, sizeof(ordwth_array[0].owflag), &ordwth_array[0].owflagInd);SQLBindCol(hstmt, 12, SQL_C_CHAR, &ordwth_array[0].ordrec, sizeof(ordwth_array[0].ordrec), &ordwth_array[0].ordrecInd);SQLBindCol(hstmt, 13, SQL_C_CHAR, &ordwth_array[0].firmid, sizeof(ordwth_array[0].firmid), &ordwth_array[0].firmidInd);SQLBindCol(hstmt, 14, SQL_C_CHAR, &ordwth_array[0].branchid, sizeof(ordwth_array[0].branchid), &ordwth_array[0].branchidInd);SQLBindCol(hstmt, 15, SQL_C_BINARY, &ordwth_array[0].checkord, sizeof(ordwth_array[0].checkord), &ordwth_array[0].checkordInd);//关闭auto commitSQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_IS_INTEGER);for (int j = 0; j < COUNT / ROW_ARRAY_SIZE; j++){for (int i = 0; i < ROW_ARRAY_SIZE; i++){ordwth_array[i].rec_num = rec_num++;ordwth_array[i].ordrecInd = 0;strcpy((char*)ordwth_array[i].date, "20150120");ordwth_array[i].dateInd = SQL_NTS;strcpy((char*)ordwth_array[i].time, "13:20:10");ordwth_array[i].timeInd = SQL_NTS;strcpy((char*)ordwth_array[i].reff, "1234567890");ordwth_array[i].reffInd = SQL_NTS;strcpy((char*)ordwth_array[i].acc, "0000011111");ordwth_array[i].accInd = SQL_NTS;strcpy((char*)ordwth_array[i].stock, "123456");ordwth_array[i].stockInd = SQL_NTS;strcpy((char*)ordwth_array[i].bs, "B");ordwth_array[i].bsInd = SQL_NTS;strcpy((char*)ordwth_array[i].price, "1.000");ordwth_array[i].priceInd = SQL_NTS;strcpy((char*)ordwth_array[i].qty, "1000");ordwth_array[i].qtyInd = SQL_NTS;strcpy((char*)ordwth_array[i].status, "R");ordwth_array[i].statusInd = SQL_NTS;strcpy((char*)ordwth_array[i].owflag, "ORD");ordwth_array[i].owflagInd = SQL_NTS;strcpy((char*)ordwth_array[i].ordrec, "1");ordwth_array[i].ordrecInd = SQL_NTS;strcpy((char*)ordwth_array[i].firmid, "123");ordwth_array[i].firmidInd = SQL_NTS;strcpy((char*)ordwth_array[i].branchid, "20201");ordwth_array[i].branchidInd = SQL_NTS;::memset(ordwth_array[i].checkord, 0, sizeof(ordwth_array[i].checkord));ordwth_array[i].checkordInd = sizeof(ordwth_array[i].checkord);}//for (int i = 0; i < ROW_ARRAY_SIZE; i++)//{//test_array[i].id = rec_num++;//strcpy((char*)test_array[i].date, "20150120");//strcpy((char*)test_array[i].abbr, "liuxing");//test_array[i].idInd = 0;//test_array[i].dateInd = SQL_NTS;//test_array[i].abbrInd = SQL_NTS;//}retcode = SQLBulkOperations(hstmt, SQL_ADD);if (retcode == SQL_ERROR){SQLCHAR msg[128];SQLCHAR state[128];SQLINTEGER error_id;SQLSMALLINT text;SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, state, &error_id, msg, 128, &text);printf("db Insert fail, sqlstate=%s, errormsg=%s\n", state, msg);SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);system("pause");return;}else if (retcode == SQL_SUCCESS_WITH_INFO){SQLCHAR msg[128];SQLCHAR state[128];SQLINTEGER error_id;SQLSMALLINT text;SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, state, &error_id, msg, 128, &text);printf("warning msg=%s\n", msg);}retcode = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);}time_t end;time(&end);printf("Insert %d records in %lld seconds, average insert speed: %lld\n", COUNT, end - begin, COUNT / (end - begin));//printf("Insert %d records in %lld seconds\n", COUNT, end - begin);system("pause");return;}

使用这种方法，每秒钟只能查6000条记录。

2. 参数批量插入

#include <windows.h>#include <sqlext.h>#include <stdio.h>#include <time.h>SQLHENV henv = NULL;SQLHDBC hdbc = NULL;SQLHSTMT hstmt = NULL;SQLRETURN retcode;#define COUNT  (100000)#define ROW_ARRAY_SIZE 1000typedef struct{SQLINTEGER rec_num;SQLINTEGER rec_numInd;SQLCHAR date[9];SQLINTEGER dateInd;SQLCHAR time[9];SQLINTEGER timeInd;SQLCHAR reff[11];SQLINTEGER reffInd;SQLCHAR acc[11];SQLINTEGER accInd;SQLCHAR stock[7];SQLINTEGER stockInd;SQLCHAR bs[2];SQLINTEGER bsInd;SQLCHAR price[9];SQLINTEGER priceInd;SQLCHAR qty[9];SQLINTEGER qtyInd;SQLCHAR status[2];SQLINTEGER statusInd;SQLCHAR owflag[4];SQLINTEGER owflagInd;SQLCHAR ordrec[9];SQLINTEGER ordrecInd;SQLCHAR firmid[6];SQLINTEGER firmidInd;SQLCHAR branchid[6];SQLINTEGER branchidInd;SQLSCHAR checkord[16];SQLINTEGER checkordInd;} ORDWTH;typedef struct{SQLINTEGER id;SQLCHAR date[20];SQLCHAR abbr[10];//SQLINTEGER idInd;SQLINTEGER dateInd;SQLLEN abbrInd;} Test;Test test_array[ROW_ARRAY_SIZE];ORDWTH  ordwth_array[ROW_ARRAY_SIZE];int rec_num = 1;void main(){retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);retcode = SQLConnect(hdbc, (SQLCHAR*) "ctp2_lx", SQL_NTS, (SQLCHAR*)"sa", SQL_NTS, (SQLCHAR*)"123456", SQL_NTS);if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){}else{SQLCHAR msg[128];SQLCHAR state[128];SQLINTEGER error_id;SQLSMALLINT text;SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1, state, &error_id, msg, 128, &text);printf("db connect fail, sqlstate=%s, errormsg=%s\n", state, msg);system("pause");return;}retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);printf("Inserting...\n");time_t begin;time(&begin);//设定SQL_ATTR_ROW_ARRAY_SIZE属性，bulk的长度//SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)ROW_ARRAY_SIZE, 0);SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER)sizeof(ORDWTH), 0);retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, (SQLPOINTER)sizeof(ORDWTH), SQL_IS_INTEGER);//设定每次参数的数量retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)(long)ROW_ARRAY_SIZE, SQL_IS_INTEGER);SQLUSMALLINT ParamStatusArray[ROW_ARRAY_SIZE] = { 0 };//设定状态数组retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, ParamStatusArray, 0);SQLINTEGER nBindOffset = 0;SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_OFFSET_PTR, (SQLPOINTER)&nBindOffset, 0);//retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER)SQL_CONCUR_ROWVER, 0);//retcode = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, 0);SQLCHAR *sql = (SQLCHAR*)"Insert into ashare_ordwth(rec_num, date, time, reff, acc, stock, bs, price, qty, status, owflag, ordrec, firmid, branchid, checkord) Values(?, ?, ?, ?, ?,?,?, ?, ?, ?, ?, ?, ?, ?, ?)";//SQLCHAR *sql = (SQLCHAR*)"Insert into ashare_ordwth(rec_num) Values(?)";SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, sizeof(ordwth_array[0].rec_num), 0, &ordwth_array[0].rec_num, sizeof(ordwth_array[0].rec_num), &ordwth_array[0].rec_numInd);    SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].date) - 1, 0, &ordwth_array[0].date, sizeof(ordwth_array[0].date), &ordwth_array[0].dateInd);    SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].time) - 1, 0, &ordwth_array[0].time, sizeof(ordwth_array[0].time), &ordwth_array[0].timeInd);SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].reff) - 1, 0, &ordwth_array[0].reff, sizeof(ordwth_array[0].reff), &ordwth_array[0].reffInd);SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].acc) - 1, 0, &ordwth_array[0].acc, sizeof(ordwth_array[0].acc), &ordwth_array[0].accInd);SQLBindParameter(hstmt, 6, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].stock) - 1, 0, &ordwth_array[0].stock, sizeof(ordwth_array[0].stock), &ordwth_array[0].stockInd);SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].bs) - 1, 0, &ordwth_array[0].bs, sizeof(ordwth_array[0].bs), &ordwth_array[0].bsInd);SQLBindParameter(hstmt, 8, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].price) - 1, 0, &ordwth_array[0].price, sizeof(ordwth_array[0].price), &ordwth_array[0].priceInd);SQLBindParameter(hstmt, 9, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].qty) - 1, 0, &ordwth_array[0].qty, sizeof(ordwth_array[0].qty), &ordwth_array[0].qtyInd);SQLBindParameter(hstmt, 10, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].status) - 1, 0, &ordwth_array[0].status, sizeof(ordwth_array[0].status), &ordwth_array[0].statusInd);SQLBindParameter(hstmt, 11, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].owflag) - 1, 0, &ordwth_array[0].owflag, sizeof(ordwth_array[0].owflag), &ordwth_array[0].owflagInd);SQLBindParameter(hstmt, 12, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].ordrec) - 1, 0, &ordwth_array[0].ordrec, sizeof(ordwth_array[0].ordrec), &ordwth_array[0].ordrecInd);SQLBindParameter(hstmt, 13, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].firmid) - 1, 0, &ordwth_array[0].firmid, sizeof(ordwth_array[0].firmid), &ordwth_array[0].firmidInd);SQLBindParameter(hstmt, 14, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(ordwth_array[0].branchid) - 1, 0, &ordwth_array[0].branchid, sizeof(ordwth_array[0].branchid), &ordwth_array[0].branchidInd);SQLBindParameter(hstmt, 15, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BINARY, sizeof(ordwth_array[0].checkord), 0, &ordwth_array[0].checkord, sizeof(ordwth_array[0].checkord), &ordwth_array[0].checkordInd);SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0);retcode = SQLPrepare(hstmt, sql, SQL_NTS);for (int j = 0; j < COUNT / ROW_ARRAY_SIZE; j++){for (int i = 0; i < ROW_ARRAY_SIZE; i++){ordwth_array[i].rec_num = rec_num++;ordwth_array[i].ordrecInd = 0;strcpy((char*)ordwth_array[i].date, "20150120");ordwth_array[i].dateInd = SQL_NTS;strcpy((char*)ordwth_array[i].time, "13:20:10");ordwth_array[i].timeInd = SQL_NTS;strcpy((char*)ordwth_array[i].reff, "1234567890");ordwth_array[i].reffInd = SQL_NTS;strcpy((char*)ordwth_array[i].acc, "0000011111");ordwth_array[i].accInd = SQL_NTS;strcpy((char*)ordwth_array[i].stock, "123456");ordwth_array[i].stockInd = SQL_NTS;strcpy((char*)ordwth_array[i].bs, "B");ordwth_array[i].bsInd = SQL_NTS;strcpy((char*)ordwth_array[i].price, "1.000");ordwth_array[i].priceInd = SQL_NTS;strcpy((char*)ordwth_array[i].qty, "1000");ordwth_array[i].qtyInd = SQL_NTS;strcpy((char*)ordwth_array[i].status, "R");ordwth_array[i].statusInd = SQL_NTS;strcpy((char*)ordwth_array[i].owflag, "ORD");ordwth_array[i].owflagInd = SQL_NTS;strcpy((char*)ordwth_array[i].ordrec, "1");ordwth_array[i].ordrecInd = SQL_NTS;strcpy((char*)ordwth_array[i].firmid, "123");ordwth_array[i].firmidInd = SQL_NTS;strcpy((char*)ordwth_array[i].branchid, "20201");ordwth_array[i].branchidInd = SQL_NTS;::memset(ordwth_array[i].checkord, 0, sizeof(ordwth_array[i].checkord));ordwth_array[i].checkordInd = sizeof(ordwth_array[i].checkord);}//执行语句retcode = SQLExecute(hstmt);if (retcode == SQL_ERROR){SQLCHAR msg[128];SQLCHAR state[128];SQLINTEGER error_id;SQLSMALLINT text;SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, state, &error_id, msg, 128, &text);printf("db Insert fail, sqlstate=%s, errormsg=%s\n", state, msg);SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);system("pause");return;}else if (retcode == SQL_SUCCESS_WITH_INFO){SQLCHAR msg[128];SQLCHAR state[128];SQLINTEGER error_id;SQLSMALLINT text;SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, state, &error_id, msg, 128, &text);printf("warning msg=%s\n", msg);}SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);}time_t end;time(&end);printf("Insert %d records in %lld seconds, average insert speed: %lld\n", COUNT, end - begin, COUNT / (end - begin));//printf("Insert %d records in %lld seconds\n", COUNT, end - begin);system("pause");return;}

使用这种方法，每秒能插16000条记录。

3. 使用OLEDB插入数据

OLEDB的介绍地址：https://msdn.microsoft.com/en-us/library/windows/desktop/ms722784(v=vs.85).aspx

sqlserver OLEDB介绍地址：https://msdn.microsoft.com/en-us/library/ms131687(v=sql.105).aspx

该例子包含两个文件：bulkcopy.h bulkcopy.cpp

bulkcopy.h

#define DBINITCONSTANTS#define OLEDBVER 0x0250   // to include correct interfaces#include <oledb.h>#include <oledberr.h>#include <stdio.h>#include <stddef.h>   // for offsetof//#include <sqlncli.h>#include "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Include\sqlncli.h"// @type UWORD    | 2 byte unsigned integer.typedef unsigned short UWORD;// @type SDWORD   | 4 byte signed integer.typedef signed long SDWORD;//委托表数据结构体struct Data{SDWORD rec_num_len;   // Length of data (not space allocated).DWORD rec_num_status;   // Status of column.int rec_num;SDWORD date_len;DWORD date_status;char date[9];SDWORD time_len;DWORD time_status;char time[9];SDWORD reff_len;DWORD reff_status;char reff[11];SDWORD acc_len;DWORD acc_status;char acc[11];SDWORD stock_len;DWORD stock_status;char stock[7];SDWORD bs_len;DWORD bs_status;char bs[2];SDWORD price_len;DWORD price_status;char price[9];SDWORD qty_len;DWORD qty_status;char qty[9];SDWORD status_len;DWORD status_status;char status[2];SDWORD owflag_len;DWORD owflag_status;char owflag[4];SDWORD ordrec_len;DWORD ordrec_status;char ordrec[9];SDWORD firmid_len;DWORD firmid_status;char firmid[6];SDWORD branchid_len;DWORD branchid_status;char branchid[6];SDWORD checkord_len;DWORD checkord_status;BYTE checkord[16];};//委托表每列的数据类型DBTYPEENUM col_type[15] = {DBTYPE_I4,DBTYPE_STR,DBTYPE_STR,DBTYPE_STR,DBTYPE_STR,DBTYPE_STR,DBTYPE_STR,DBTYPE_STR,DBTYPE_STR,DBTYPE_STR,DBTYPE_STR,DBTYPE_STR,DBTYPE_STR,DBTYPE_STR,DBTYPE_BYTES};//委托表没列的数据长度DBBYTEOFFSET col_len[15] = {sizeof(int),9,9,11,11,7,2,9,9,2,4,9,6,6,16};// How to lay out each column in memory.struct COLUMNDATA {SDWORD idLen;   // Length of data (not space allocated).DWORD idStatus;   // Status of column.int id;   // Store data here as a variant.SDWORD dateLen;DWORD dateStatus;char date[21];};//函数申明void set_bindings();//一个绑定void set_bind(DBBINDING &binding, int col, DBBYTEOFFSET len_offset, DBBYTEOFFSET status_offset, DBBYTEOFFSET value_offset, DBLENGTH len, DBTYPE type);// Given an ICommand pointer, properties, and query, a rowsetpointer is returned.HRESULT CreateSessionCommand(DBPROPSET* rgPropertySets, ULONG ulcPropCount, CLSID clsidProv);// Use to set properties and execute a given query.HRESULT ExecuteQuery(IDBCreateCommand* pIDBCreateCommand,WCHAR* pwszQuery,DBPROPSET* rgPropertySets,ULONG ulcPropCount,LONG* pcRowsAffected,IRowset** ppIRowset,BOOL fSuccessOnly = TRUE);void DumpErrorInfo(IUnknown* pObjectWithError, REFIID IID_InterfaceWithError);// Use to set up options for call to IDBInitialize::Initialize.void SetupOption(DBPROPID PropID, WCHAR *wszVal, DBPROP * pDBProp);// Sets fastload property on/off for session.HRESULT SetFastLoadProperty(BOOL fSet);HRESULT FastInsertData();

bulkcopy.cpp

#include "bulkcopy.h"#include <time.h>#define COUNT  1000000#define ROW_SIZE  1000#define COLUMN_ALIGNVAL 8#define ROUND_UP(Size, Amount)(((DWORD)(Size) + ((Amount)-1)) & ~((Amount)-1))WCHAR g_wszTable[] = L"ashare_ordwth";//WCHAR g_wszTable[] = L"test";WCHAR g_strTestLOC[100] = L"172.19.124.72";WCHAR g_strTestDBName[] = L"ctp2_lx";const UWORD g_cOPTION = 5;const UWORD MAXPROPERTIES = 5;const ULONG DEFAULT_CBMAXLENGTH = 20;IMalloc* g_pIMalloc = NULL;IDBInitialize* g_pIDBInitialize = NULL;int rec_num = 1;//binding数组DBBINDING g_bindings[15];int main() {HRESULT hr = NOERROR;HRESULT hr2 = NOERROR;// OLE initialized?BOOL fInitialized = FALSE;// One property set for initializing.DBPROPSET rgPropertySets[1];// Properties within above property set.DBPROP rgDBProperties[g_cOPTION];IDBCreateCommand* pIDBCreateCommand = NULL;IRowset* pIRowset = NULL;DBPROPSET* rgProperties = NULL;IAccessor* pIAccessor = NULL;// Basic initialization.if (FAILED(CoInitialize(NULL)))goto cleanup;elsefInitialized = TRUE;hr = CoGetMalloc(MEMCTX_TASK, &g_pIMalloc);if ((!g_pIMalloc) || FAILED(hr))goto cleanup;// Set up property set for call to IDBInitialize in CreateSessionCommand.rgPropertySets[0].rgProperties = rgDBProperties;rgPropertySets[0].cProperties = g_cOPTION;rgPropertySets[0].guidPropertySet = DBPROPSET_DBINIT;SetupOption(DBPROP_INIT_CATALOG, L"ctp2_lx", &rgDBProperties[0]);//SetupOption(DBPROP_INIT_DATASOURCE, L"ctp2_lx", &rgDBProperties[0]);//SetupOption(DBPROP_AUTH_INTEGRATED, L"SSPI", &rgDBProperties[1]);SetupOption(DBPROP_INIT_DATASOURCE, L"172.19.124.72", &rgDBProperties[1]);//SetupOption(DBPROP_INIT_LOCATION, L"172.19.124.72", &rgDBProperties[2]);//密码SetupOption(DBPROP_AUTH_PASSWORD, L"123456", &rgDBProperties[3]);//用户名SetupOption(DBPROP_AUTH_USERID, L"sa", &rgDBProperties[4]);if (!SUCCEEDED(hr = CreateSessionCommand(rgPropertySets, 1, SQLNCLI_CLSID)))goto cleanup;time_t begin;time(&begin);// Get IRowsetFastLoad and insert data into IRFLTable.if (FAILED(hr = FastInsertData()))goto cleanup;time_t end;time(&end);printf("Elapse Time= [%lld]\n", end - begin);cleanup:// Release memory.if (rgProperties && rgProperties->rgProperties)delete[](rgProperties->rgProperties);if (rgProperties)delete[]rgProperties;if (pIDBCreateCommand)pIDBCreateCommand->Release();if (pIAccessor)pIAccessor->Release();if (pIRowset)pIRowset->Release();if (g_pIMalloc)g_pIMalloc->Release();if (g_pIDBInitialize) {hr2 = g_pIDBInitialize->Uninitialize();if (FAILED(hr2))printf("Uninitialize failed\n");}if (fInitialized)CoUninitialize();if (SUCCEEDED(hr))printf("Test completed successfully.\n\n");elseprintf("Test failed.\n\n");system("pause");}void set_bindings(){set_bind(g_bindings[0], 1, offsetof(Data, rec_num_len), offsetof(Data, rec_num_status), offsetof(Data, rec_num), col_len[0], col_type[0]);set_bind(g_bindings[1], 2, offsetof(Data, date_len), offsetof(Data, date_status), offsetof(Data, date), col_len[1], col_type[1]);set_bind(g_bindings[2], 3, offsetof(Data, time_len), offsetof(Data, time_status), offsetof(Data, time), col_len[2], col_type[2]);set_bind(g_bindings[3], 4, offsetof(Data, reff_len), offsetof(Data, reff_status), offsetof(Data, reff), col_len[3], col_type[3]);set_bind(g_bindings[4], 5, offsetof(Data, acc_len), offsetof(Data, acc_status), offsetof(Data, acc), col_len[4], col_type[4]);set_bind(g_bindings[5], 6, offsetof(Data, stock_len), offsetof(Data, stock_status), offsetof(Data, stock), col_len[5], col_type[5]);set_bind(g_bindings[6], 7, offsetof(Data, bs_len), offsetof(Data, bs_status), offsetof(Data, bs), col_len[6], col_type[6]);set_bind(g_bindings[7], 8, offsetof(Data, price_len), offsetof(Data, price_status), offsetof(Data, price), col_len[7], col_type[7]);set_bind(g_bindings[8], 9, offsetof(Data, qty_len), offsetof(Data, qty_status), offsetof(Data, qty), col_len[8], col_type[8]);set_bind(g_bindings[9], 10, offsetof(Data, status_len), offsetof(Data, status_status), offsetof(Data, status), col_len[9], col_type[9]);set_bind(g_bindings[10], 11, offsetof(Data, owflag_len), offsetof(Data, owflag_status), offsetof(Data, owflag), col_len[10], col_type[10]);set_bind(g_bindings[11], 12, offsetof(Data, ordrec_len), offsetof(Data, ordrec_status), offsetof(Data, ordrec), col_len[11], col_type[11]);set_bind(g_bindings[12], 13, offsetof(Data, firmid_len), offsetof(Data, firmid_status), offsetof(Data, firmid), col_len[12], col_type[12]);set_bind(g_bindings[13], 14, offsetof(Data, branchid_len), offsetof(Data, branchid_status), offsetof(Data, branchid), col_len[13], col_type[13]);set_bind(g_bindings[14], 15, offsetof(Data, checkord_len), offsetof(Data, checkord_status), offsetof(Data, checkord), col_len[14], col_type[14]);}void set_bind(DBBINDING &binding, int col, DBBYTEOFFSET len_offset, DBBYTEOFFSET status_offset, DBBYTEOFFSET value_offset, DBLENGTH len, DBTYPE type){binding.dwPart = DBPART_VALUE | DBPART_LENGTH | DBPART_STATUS;binding.iOrdinal = col;binding.pTypeInfo = NULL;binding.obValue = value_offset;binding.obLength = len_offset;binding.obStatus = status_offset;binding.cbMaxLen = len;   // Size of varchar column.binding.pTypeInfo = NULL;binding.pObject = NULL;binding.pBindExt = NULL;binding.dwFlags = 0;binding.eParamIO = DBPARAMIO_NOTPARAM;binding.dwMemOwner = DBMEMOWNER_CLIENTOWNED;binding.bPrecision = 0;binding.bScale = 0;binding.wType = type;}//插入委托表HRESULT FastInsertData() {HRESULT hr = E_FAIL;HRESULT hr2 = E_FAIL;DBID TableID;IDBCreateSession* pIDBCreateSession = NULL;IOpenRowset* pIOpenRowsetFL = NULL;IRowsetFastLoad* pIFastLoad = NULL;IAccessor* pIAccessor = NULL;HACCESSOR hAccessor = 0;DBBINDSTATUS bindingStatus[15] = { 0 };TableID.uName.pwszName = NULL;LONG i = 0;void* pData = NULL;TableID.eKind = DBKIND_NAME;// if ( !(TableID.uName.pwszName = new WCHAR[wcslen(g_wszTable) + 2]) )LPOLESTR x = TableID.uName.pwszName = new WCHAR[wcslen(g_wszTable) + 2];if (!x)return E_FAIL;wcsncpy_s(TableID.uName.pwszName, wcslen(g_wszTable) + 2, g_wszTable, wcslen(g_wszTable) + 1);TableID.uName.pwszName[wcslen(g_wszTable) + 1] = (WCHAR)NULL;// Get the fastload pointer.if (FAILED(hr = SetFastLoadProperty(TRUE)))goto cleanup;if (FAILED(hr =g_pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void **)&pIDBCreateSession)))goto cleanup;if (FAILED(hr =pIDBCreateSession->CreateSession(NULL, IID_IOpenRowset, (IUnknown **)&pIOpenRowsetFL)))goto cleanup;// Get IRowsetFastLoad initialized to use the test table.if (FAILED(hr =pIOpenRowsetFL->OpenRowset(NULL,&TableID,NULL,IID_IRowsetFastLoad,0,NULL,(LPUNKNOWN *)&pIFastLoad)))goto cleanup;set_bindings();if (FAILED(hr =pIFastLoad->QueryInterface(IID_IAccessor, (void **)&pIAccessor)))return hr;if (FAILED(hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA,15,g_bindings,ROUND_UP(sizeof(Data), COLUMN_ALIGNVAL),&hAccessor,bindingStatus)))return hr;Data *datas = new Data[COUNT];for (int i = 0; i < COUNT; i++){datas[i].rec_num_len = col_len[0];datas[i].date_len = col_len[1];datas[i].time_len = col_len[2];datas[i].reff_len = col_len[3];datas[i].acc_len = col_len[4];datas[i].stock_len = col_len[5];datas[i].bs_len = col_len[6];datas[i].price_len = col_len[7];datas[i].qty_len = col_len[8];datas[i].status_len = col_len[9];datas[i].owflag_len = col_len[10];datas[i].ordrec_len = col_len[11];datas[i].firmid_len = col_len[12];datas[i].branchid_len = col_len[13];datas[i].checkord_len = col_len[14];datas[i].rec_num_status = 0;datas[i].date_status = 0;datas[i].time_status = 0;datas[i].reff_status = 0;datas[i].acc_status = 0;datas[i].stock_status = 0;datas[i].bs_status = 0;datas[i].price_status = 0;datas[i].qty_status = 0;datas[i].status_status = 0;datas[i].owflag_status = 0;datas[i].ordrec_status = 0;datas[i].firmid_status = 0;datas[i].branchid_status = 0;datas[i].checkord_status = 0;}for (int i = 0; i < COUNT; i++){datas[i].rec_num = rec_num++;strncpy(datas[i].date, "20150120", col_len[1]);strncpy(datas[i].time, "13:20:10", col_len[2]);strncpy(datas[i].reff, "1234567890", col_len[3]);strncpy(datas[i].acc, "0000011111", col_len[4]);strncpy(datas[i].stock, "123456", col_len[5]);strncpy(datas[i].bs, "B", col_len[6]);strncpy(datas[i].price, "1.000", col_len[7]);strncpy(datas[i].qty, "1000", col_len[8]);strncpy(datas[i].status, "R", col_len[9]);strncpy(datas[i].owflag, "ORD", col_len[10]);strncpy(datas[i].ordrec, "1", col_len[11]);strncpy(datas[i].firmid, "123", col_len[12]);strncpy(datas[i].branchid, "20201", col_len[13]);memset(datas[i].checkord, 0, col_len[14]);if (FAILED(hr = pIFastLoad->InsertRow(hAccessor, &datas[i]))){DumpErrorInfo(pIFastLoad, IID_ISQLServerErrorInfo);goto cleanup;}if (FAILED(hr = pIFastLoad->Commit(TRUE))){DumpErrorInfo(pIFastLoad, IID_ISQLServerErrorInfo);printf("Error on IRFL::Commit\n");}}cleanup:if (FAILED(hr2 = SetFastLoadProperty(FALSE)))printf("SetFastLoadProperty(FALSE) failed with %x", hr2);if (pIAccessor && hAccessor)if (FAILED(pIAccessor->ReleaseAccessor(hAccessor, NULL)))hr = E_FAIL;if (pIAccessor)pIAccessor->Release();if (pIFastLoad)pIFastLoad->Release();if (pIOpenRowsetFL)pIOpenRowsetFL->Release();if (pIDBCreateSession)pIDBCreateSession->Release();if (TableID.uName.pwszName)delete[]TableID.uName.pwszName;delete[] datas;return hr;}HRESULT SetFastLoadProperty(BOOL fSet) {HRESULT hr = S_OK;IDBProperties* pIDBProps = NULL;DBPROP rgProps[1];DBPROPSET PropSet;VariantInit(&rgProps[0].vValue);rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;rgProps[0].colid = DB_NULLID;rgProps[0].vValue.vt = VT_BOOL;rgProps[0].dwPropertyID = SSPROP_ENABLEFASTLOAD;if (fSet == TRUE)rgProps[0].vValue.boolVal = VARIANT_TRUE;elsergProps[0].vValue.boolVal = VARIANT_FALSE;PropSet.rgProperties = rgProps;PropSet.cProperties = 1;PropSet.guidPropertySet = DBPROPSET_SQLSERVERDATASOURCE;if (SUCCEEDED(hr = g_pIDBInitialize->QueryInterface(IID_IDBProperties, (LPVOID *)&pIDBProps)))hr = pIDBProps->SetProperties(1, &PropSet);VariantClear(&rgProps[0].vValue);if (pIDBProps)pIDBProps->Release();return hr;}HRESULT CreateSessionCommand(DBPROPSET* rgPropertySets,// @parm [in] property setsULONG ulcPropCount,   // @parm [in] count of prop sets.CLSID clsidProv) {  // @parm [in] Provider CLSID.HRESULT hr = NOERROR;IDBCreateSession* pIDBCreateSession = NULL;IDBProperties* pIDBProperties = NULL;UWORD i = 0, j = 0;   // indexes.if (ulcPropCount && !rgPropertySets) {hr = E_INVALIDARG;return hr;}if (!SUCCEEDED(hr = CoCreateInstance(clsidProv,NULL, CLSCTX_INPROC_SERVER,IID_IDBInitialize,(void **)&g_pIDBInitialize)))goto CLEANUP;if (!SUCCEEDED(hr = g_pIDBInitialize->QueryInterface(IID_IDBProperties,(void **)&pIDBProperties)))goto CLEANUP;if (!SUCCEEDED(hr = pIDBProperties->SetProperties(ulcPropCount, rgPropertySets)))goto CLEANUP;if (!SUCCEEDED(hr = g_pIDBInitialize->Initialize())) {printf("Call to initialize failed.\n");goto CLEANUP;}CLEANUP:if (pIDBProperties)pIDBProperties->Release();if (pIDBCreateSession)pIDBCreateSession->Release();for (i = 0; i < ulcPropCount; i++)for (j = 0; j < rgPropertySets[i].cProperties; j++)VariantClear(&(rgPropertySets[i].rgProperties[j]).vValue);return hr;}void SetupOption(DBPROPID PropID, WCHAR *wszVal, DBPROP * pDBProp) {pDBProp->dwPropertyID = PropID;pDBProp->dwOptions = DBPROPOPTIONS_REQUIRED;pDBProp->colid = DB_NULLID;pDBProp->vValue.vt = VT_BSTR;pDBProp->vValue.bstrVal = SysAllocStringLen(wszVal, wcslen(wszVal));}// DumpErrorInfo queries SQLOLEDB error interfaces, retrieving available// status or error information.void DumpErrorInfo(IUnknown* pObjectWithError,REFIID IID_InterfaceWithError){// Interfaces used in the example.IErrorInfo*             pIErrorInfoAll = NULL;IErrorInfo*             pIErrorInfoRecord = NULL;IErrorRecords*          pIErrorRecords = NULL;ISupportErrorInfo*      pISupportErrorInfo = NULL;ISQLErrorInfo*          pISQLErrorInfo = NULL;ISQLServerErrorInfo*    pISQLServerErrorInfo = NULL;// Number of error records.ULONG                   nRecs;ULONG                   nRec;// Basic error information from GetBasicErrorInfo.ERRORINFO               errorinfo;// IErrorInfo values.BSTR                    bstrDescription;BSTR                    bstrSource;// ISQLErrorInfo parameters.BSTR                    bstrSQLSTATE;LONG                    lNativeError;// ISQLServerErrorInfo parameter pointers.SSERRORINFO*            pSSErrorInfo = NULL;OLECHAR*                pSSErrorStrings = NULL;// Hard-code an American English locale for the example.DWORD                   MYLOCALEID = 0x0409;// Only ask for error information if the interface supports// it.if (FAILED(pObjectWithError->QueryInterface(IID_ISupportErrorInfo,(void**)&pISupportErrorInfo))){wprintf_s(L"SupportErrorErrorInfo interface not supported");return;}if (FAILED(pISupportErrorInfo->InterfaceSupportsErrorInfo(IID_InterfaceWithError))){wprintf_s(L"InterfaceWithError interface not supported");return;}// Do not test the return of GetErrorInfo. It can succeed and return// a NULL pointer in pIErrorInfoAll. Simply test the pointer.GetErrorInfo(0, &pIErrorInfoAll);if (pIErrorInfoAll != NULL){// Test to see if it's a valid OLE DB IErrorInfo interface // exposing a list of records.if (SUCCEEDED(pIErrorInfoAll->QueryInterface(IID_IErrorRecords,(void**)&pIErrorRecords))){pIErrorRecords->GetRecordCount(&nRecs);// Within each record, retrieve information from each// of the defined interfaces.for (nRec = 0; nRec < nRecs; nRec++){// From IErrorRecords, get the HRESULT and a reference// to the ISQLErrorInfo interface.pIErrorRecords->GetBasicErrorInfo(nRec, &errorinfo);pIErrorRecords->GetCustomErrorObject(nRec,IID_ISQLErrorInfo, (IUnknown**)&pISQLErrorInfo);// Display the HRESULT, then use the ISQLErrorInfo.wprintf_s(L"HRESULT:\t%#X\n", errorinfo.hrError);if (pISQLErrorInfo != NULL){pISQLErrorInfo->GetSQLInfo(&bstrSQLSTATE,&lNativeError);// Display the SQLSTATE and native error values.wprintf_s(L"SQLSTATE:\t%s\nNative Error:\t%ld\n",bstrSQLSTATE, lNativeError);// SysFree BSTR references.SysFreeString(bstrSQLSTATE);// Get the ISQLServerErrorInfo interface from// ISQLErrorInfo before releasing the reference.pISQLErrorInfo->QueryInterface(IID_ISQLServerErrorInfo,(void**)&pISQLServerErrorInfo);pISQLErrorInfo->Release();}// Test to ensure the reference is valid, then// get error information from ISQLServerErrorInfo.if (pISQLServerErrorInfo != NULL){pISQLServerErrorInfo->GetErrorInfo(&pSSErrorInfo,&pSSErrorStrings);// ISQLServerErrorInfo::GetErrorInfo succeeds// even when it has nothing to return. Test the// pointers before using.if (pSSErrorInfo){// Display the state and severity from the// returned information. The error message comes// from IErrorInfo::GetDescription.wprintf_s(L"Error state:\t%d\nSeverity:\t%d\n",pSSErrorInfo->bState,pSSErrorInfo->bClass);// IMalloc::Free needed to release references// on returned values. For the example, assume// the g_pIMalloc pointer is valid.g_pIMalloc->Free(pSSErrorStrings);g_pIMalloc->Free(pSSErrorInfo);}pISQLServerErrorInfo->Release();}if (SUCCEEDED(pIErrorRecords->GetErrorInfo(nRec,MYLOCALEID, &pIErrorInfoRecord))){// Get the source and description (error message)// from the record's IErrorInfo.pIErrorInfoRecord->GetSource(&bstrSource);pIErrorInfoRecord->GetDescription(&bstrDescription);if (bstrSource != NULL){wprintf_s(L"Source:\t\t%s\n", bstrSource);SysFreeString(bstrSource);}if (bstrDescription != NULL){wprintf_s(L"Error message:\t%s\n",bstrDescription);SysFreeString(bstrDescription);}pIErrorInfoRecord->Release();}}pIErrorRecords->Release();}else{// IErrorInfo is valid; get the source and// description to see what it is.pIErrorInfoAll->GetSource(&bstrSource);pIErrorInfoAll->GetDescription(&bstrDescription);if (bstrSource != NULL){wprintf_s(L"Source:\t\t%s\n", bstrSource);SysFreeString(bstrSource);}if (bstrDescription != NULL){wprintf_s(L"Error message:\t%s\n", bstrDescription);SysFreeString(bstrDescription);}}pIErrorInfoAll->Release();}else{wprintf_s(L"GetErrorInfo failed.");}pISupportErrorInfo->Release();return;}

使用上面方法，每秒钟可以插入3万多条记录。

OLEDB的接口确实有点繁琐~有时候读了好多遍MSDN上的接口说明，也执行不正确。