如何用ADO控件将VB中的大量数据快速写入SQL server数据库？

Question

程序如下：将ArrData()中的数据写入表TableName 中。
Private Function TODBS(TableName As String, ValueField As String, ArrData() As Integer)
    Dim i As Integer
    Dim LenArr As Long
    Dim Cnt As ADODB.Connection
    Dim strSQL As String
    Dim tempStr As String    LenArr = UBound(ArrData)    Set Cnt = New ADODB.Connection    '将对象引用赋给变量或属性
    Cnt.Open strCn    '保存数据 TO 数据表
     For i = 0 To LenArr
        strSQL = "INSERT INTO " & TableName & " (test_SN," & ValueField & ") VALUES ('" & _
                 TestNo & "'," & ArrData(i) & ")"
        Cnt.Execute strSQL  '此语句耗时2ms!!!
    Next    Set Cnt = NothingEnd Function效率实在太低！望各位大侠不吝赐教！！

Answer 1

'//将SQL语句用分号分隔.一次提次 50 条. 
Private   Function   TODBS(TableName   As   String,   ValueField   As   String,   ArrData()   As   Integer) 
        Dim   i   As   Integer 
        Dim   LenArr   As   Long 
        Dim   Cnt   As   ADODB.Connection 
        Dim   strSQL   As   String 
        Dim   tempStr   As   String 
 
        LenArr   =   UBound(ArrData)         Set   Cnt   =   New   ADODB.Connection         '将对象引用赋给变量或属性 
        Cnt.Open   strCn         '保存数据   TO   数据表 
          For   i   =   0   To   LenArr 
if i mod 50 =0 then 
                strSQL   =  strSQL   &  "INSERT   INTO   "   &   TableName   &   "   (test_SN,"   &   ValueField   &   ")   VALUES   ('"   &   _ 
                                  TestNo   &   "',"   &   ArrData(i)   &   ");" 
                Cnt.Execute   strSQL     '此语句耗时2ms!!! 
    strsql=""
end if
                strSQL   =  strSQL   &  "INSERT   INTO   "   &   TableName   &   "   (test_SN,"   &   ValueField   &   ")   VALUES   ('"   &   _ 
                                  TestNo   &   "',"   &   ArrData(i)   &   ");" 
                Cnt.Execute   strSQL     '此语句耗时2ms!!!         Next         Set   Cnt   =   Nothing End   Function 

Answer 2

THANK YOU! Mr. Chen！速度提高了三倍，不过还是太慢，现在存1M的数据大概需要5～7分钟！我采集到的数据通常大于10M,难道只能等等等。

Answer 3

再要提高的话,只能写存储过程了.CREATE PROC PRC_INSERTINTO(
@VAR_TABLE VARCHAR(128),--表名.
@VAR_VALUEFIELD VARCHAR(256),--字段列表.
@VAR_VAL VARCHAR(8000) -- 值用分号分隔的序列值。如： 'A',1;'B',2
) AS BEGIN
DECLARE @VAR_CMD VARCHAR(8000)
SET @VAR_VAL=';' + @VAR_VAL
SET @VAR_CMD=REPLACE(@VAR_VAL,';',');INSERT INTO ' + @VAR_TABLE + ' (' + @VAR_VALUEFIELD + ') VALUES(' 
SET @VAR_CMD=LEFT(@VAR_CMD,LEN(@VAR_CMD)-1) + ')'
EXECUTE @VAR_CMD
END直接调用.前台以 100,或 200 或更多按值格式调用(要以调试出一个值,最优值上调用该过程出错前的值).即可.
跟你原来比,应该可以提高10倍以上.

Answer 4

改一下CREATE   PROC   PRC_INSERTINTO( 
@VAR_TABLE VARCHAR(128),--表名. 
@VAR_VALUEFIELD VARCHAR(256),--字段列表. 
@VAR_VAL VARCHAR(8000)   --   值用分号分隔的序列值。如：   'A',1;'B',2 
)   AS   BEGIN 
DECLARE   @VAR_CMD VARCHAR(8000) 
SET   @VAR_VAL= @VAR_VAL 
SET   @VAR_CMD=REPLACE(@VAR_VAL,';',');INSERT INTO '   +   @VAR_TABLE   +   ' (' + @VAR_VALUEFIELD   +   ')   VALUES('   
SET   @VAR_CMD='INSERT   INTO   '   +   @VAR_TABLE   +   ' ('   +   @VAR_VALUEFIELD   +   ')   VALUES(' + @VAR_CMD   +   ')' 
EXECUTE   （@VAR_CMD ）
END 

Answer 5

 Mr.Chen，
    非常感谢您提供的帮助！！但我还有一个小麻烦，我用的SQL数据库是远程的，我无权直接对它操作，请陈先生再指点一下，我如何在VB中创建存储过程呀？万分感激！！  _zjgno1

Answer 6

  Dim AdoConn As New ADODB.Connection
  Dim StrSql As String
    StrSql = " IF NOT EXISTS(SELECT * FROM DBO.SYSOBJECTS) BEGIN " & _
            "CREATE PROC PRC_INSERTINTO( " & Chr(13) & _
                "@VAR_TABLE VARCHAR(128)," & Chr(13) & _
                "@VAR_VALUEFIELD VARCHAR(256)," & Chr(13) & _
                "@VAR_VAL VARCHAR(8000)" & Chr(13) & _
            ") AS BEGIN" & Chr(13) & _
                    "DECLARE @VAR_CMD VARCHAR(8000)" & Chr(13) & _
                    "SET @VAR_VAL= @VAR_VAL" & Chr(13) & _
                    "SET @VAR_CMD=REPLACE(@VAR_VAL,';',');INSERT INTO ' + @VAR_TABLE + ' (' + @VAR_VALUEFIELD + ') VALUES('" & Chr(13) & _
                    "SET @VAR_CMD='INSERT INTO ' + @VAR_TABLE + ' (' + @VAR_VALUEFIELD + ') VALUES(' + @VAR_CMD + ')'" & Chr(13) & _
                    "EXECUT (@VAR_CMD)" & Chr(13) & _
                "End" & Chr(13) _
            & "End"
 Call AdoConn.Execute(StrSql)

Answer 7

上面错了.改一下.    Dim   AdoConn   As   New   ADODB.Connection 
    Dim   StrSql   As   String 
        StrSql   =   "   IF   NOT   EXISTS(SELECT   *   FROM   DBO.SYSOBJECTS WHERE [NAME]='PRC_INSERTINTO')   BEGIN   "   &   _ 
                        "CREATE   PROC   PRC_INSERTINTO(   "   &   Chr(13)   &   _ 
                                "@VAR_TABLE   VARCHAR(128),"   &   Chr(13)   &   _ 
                                "@VAR_VALUEFIELD   VARCHAR(256),"   &   Chr(13)   &   _ 
                                "@VAR_VAL   VARCHAR(8000)"   &   Chr(13)   &   _ 
                        ")   AS   BEGIN"   &   Chr(13)   &   _ 
                                        "DECLARE   @VAR_CMD   VARCHAR(8000)"   &   Chr(13)   &   _ 
                                        "SET   @VAR_VAL=   @VAR_VAL"   &   Chr(13)   &   _ 
                                        "SET   @VAR_CMD=REPLACE(@VAR_VAL,';',');INSERT   INTO   '   +   @VAR_TABLE   +   '   ('   +   @VAR_VALUEFIELD   +   ')   VALUES('"   &   Chr(13)   &   _ 
                                        "SET   @VAR_CMD='INSERT   INTO   '   +   @VAR_TABLE   +   '   ('   +   @VAR_VALUEFIELD   +   ')   VALUES('   +   @VAR_CMD   +   ')'"   &   Chr(13)   &   _ 
                                        "EXECUT   (@VAR_CMD)"   &   Chr(13)   &   _ 
                                "End"   &   Chr(13)   _ 
                        &   "End" 
  Call   AdoConn.Execute(StrSql)