How to do BULK-ADD in VBA

[Ernesto]

The follwoing scheme istaking a huge amount of time to update sql server:

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM " & tableName, getConn, adOpenKeyset,
adLockBatchOptimistic
For I = 1 To N
rs.AddNew
For J = 1 To FieldCount
rs.Fields(FieldArray(J)) = data(I, J)
Next J
'rs.Update
Next I
rs.UpdateBatch



Is there a better way?
is there a way to call ODBC API routines such as BULKOPERATIONS from within
VBA?

Any help will be most appreciated

 

[Ron Weiner]

Ernesto

You did not tell us where the source data is coming from but if it is in a
table you can construct and execute a single sql statement that inserts all
of the rows and columns from the source table to the destination table all
at once. Something like
INSERT INTO tblDest SELECT * FROM tblSource
will insert all of the records from tblSource into tblDest PDQ.

If your source data is in a array then you are limited to inserting one row
at a time using something like
INSERT INTO testbp ( Column1, Column2 , ColumnN, ... )
VALUES (data(I, 0) , data(I, 1) , data(I, N) , ...)

This will still be many times faster than looping through all of the columns
of all of the rows, as you are doing now.

If we had more information about your environment we could likely offer
better or additional solutions.

Ron W

 

[Ernesto]

Thanks for your reply.
I assumed that it was clear that the source was an array which is the result
of a computation. Sorry!
You are right in that I do not need to loop through the columns, and there
are only a few.
But their names and number are not known until runtime, so the query string
needs to be constructed which is not a big task, and I can try it to see if
it helps.
But Mainly the number of rows is relatively large. And that is the real
hangup.

I know how to do a BulkInsert from within a regular programming language,
but I do no tknow how to do it in VBA.
Else I was wondering if there is a stored procedure I can use to write an
array to the table.

Thanks,

 

[¹Ú¹®¼ö]

The follwoing scheme istaking a huge amount of time to update sql server:

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM " & tableName, getConn, adOpenKeyset,
adLockBatchOptimistic
For I = 1 To N
rs.AddNew
For J = 1 To FieldCount
rs.Fields(FieldArray(J)) = data(I, J)
Next J
'rs.Update
Next I
rs.UpdateBatch



Is there a better way?
is there a way to call ODBC API routines such as BULKOPERATIONS from within
VBA?

Any help will be most appreciated