UpdateBatch alternative needed

  • Thread starter Thread starter Ernesto
  • Start date Start date
E

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
 
rs.Open "SELECT * FROM " & tableName, getConn, adOpenKeyset,
adLockBatchOptimistic
For I = 1 To N
rs.AddNew
Click to expand...

Why on earth are you loading a whole table, just in order to add a record?
You don't need to fetch _anything_.

strSQL = "SELECT * FROM MyTable WHERE FALSE"
rs.Open strSQL, etc, etc.

will be much kinder to everyone else who wants to use the network.

In any case, what about using an INSERT query in the first place?

strSQL = "INSERT INTO MyTable (FieldOne, FieldTwo) VALUES ("

strSQL = strSQL & Format(data(1), strFormatStringA) & ", "
strSQL = strSQL & Format(data(2), strFormatStringA) & ", "
strSQL = strSQL & Format(data(3), strFormatStringN) & ", "
strSQL = strSQL & Format(data(4), strFormatStringD) & ") "


conn.Execute strSQL, etc etc.

Hope that helps


Tim F
 
Forgive me if these responses seem stupid.

1. I suppose I did not mentioned The table is empty and is being written
from the beginning, though your query seems better anyway.
2. There are thousands of records to be written. The individual Insert will
likely take even longer then the batch update.

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 would like to know if I can write a stored procedure on the server
which can take in the array and add it faster than the batchupdate.

Thanks,
 
2. There are thousands of records to be written. The individual Insert
will likely take even longer then the batch update.
Click to expand...

Where are these thousands of records coming from?

If it's another table, then you just need an INSERT INTO... SELECT command.

If it's a non-database source like a text file, then _anything_ you do is
only going to be a cover for INSERT.... ("Eric", 2392, #2002-13-09#) even
if it's hidden behind a bunch of binary toolkits and libraries. I have
never been able to detect a serious speed differences with local databases
between handling recordsets and proper queries; and I _know_ which I'd like
to come back to in two years' time. You aren't doing a batch update anyway.

B Wishes

Tim F
 
Back
Top