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

[zjgno1]

程序如下：将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 = Nothing

End Function

效率实在太低！望各位大侠不吝赐教！！

[华芸智森]

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

[zjgno1]

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

[华芸智森]

再要提高的话,只能写存储过程了.

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倍以上.

[华芸智森]

改一下

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

[华芸智森]

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)

[华芸智森]

上面错了.改一下.

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)

[zjgno1]

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