mass insert

[dejfatman]

I am reading a text file as input and inserting some data into a table. I have a while loop that reads a record, and then inserts into the table, but the process is incredibly slow. I thought there was a way to do something like:

insert into table
values ("a","b","c")
("d","e","f")
("g","h","i")...etc.

This doesn't seem to work in Access. Is there a better way to do this?

 

[ByteMyzer]

In Access you can structure an Append Query like the following example:
[tt]
INSERT INTO TABLE1 ( PRODUCT, QTY , DATE )
SELECT "Chair", 4, #4/22/2004#;
[/tt]

This will insert a single record into TABLE1, with the following data:
[tt]
PRODUCT | QTY | DATE
-------------------------
Chair | 4 | 4/22/2004
[/tt]

 

[dejfatman]

Thanks, but I need to insert 100's of rows into the table. The code right now is basically:

While not EOF(1)
strSQL = "insert into <table> values(<row data>)
rcd.Open strSQL, cn, adOpenKeyset, adLockOptimistic
Line Input #1, <row data>
Wend

So, for each line of input it reads from the input file, it has to open the connection and run the insert. With hundreds of rows, it goes very slowly. I am wondering if there is a way to do multiple inserts in one SQL statement. I cannot really use a select within the insert because the data resides on an external file, not an Access table.

 

[ByteMyzer]

You don't have to open the connection for every row, simply open the connection at the beginning. Also use a QueryDef. Example:

' code for opening the connection and your source file here
' ...

Dim qdfTemp As QueryDef

Set qdfTemp = cn.CreateQueryDef("")

While Not EOF(1)
    With qdfTemp
        .Prepare = dbQUnprepare
        .SQL = "insert into <table> values(<row data>)"
        .Execute
    End With
    Line Input #1, <row data>
Wend

qdfTemp.Close

Then, when you reach the end of the file, you can close the connection. This saves astronomical amounts of time not closing and re-opening the connection. Since you are using a Connection, I gather this is an ODBC workspace? To MySQL, for example?

[tt]DISCLAIMER:[/tt] The above code is not necessarily meant to be used literally. Appropriate substitutions for the actual variables and/or SQL statements should be made. You're not really the sort of person who walks up to someone at a party and says, "Hi, my name is Your Name", are you?

 

[dejfatman]

Thanks ByteMyzer, I did find a solution similar to the one you suggested, like so:

rcd.Open "tmpTables", cn, adOpenKeyset, adLockOptimistic, adCmdTable
....
With rcd
.AddNew
!TableType = txtType
!AccountNumber = Mid(inline, 7, 16)
!ReturnDigit = Mid(inline, 23, 10)
.Update
End With

What is the advantage of using the QueryDef as you described?

Since you are using a Connection, I gather this is an ODBC workspace? To MySQL, for example?
No, I'm just writing to a regular Access table.

 

[ByteMyzer]

The advantage of a querydef, in the case of a connection to an ODBC Workspace, is both a means of extracting data from the ODBCSource, and as a way of executing Pass-Through SQL Action Queries.

Since you are only updating Access Tables, you d not even need a querydef or a connection object. Instead of the [tt]With rcd...End With[/tt] code above, simply use a single statement like the following example:

DBEngine(0)(0).Execute "INSERT INTO tmpTables (TableType, AccountNumber, ReturnDigit) " _
    & "SELECT '" & txtType & "', '" & Mid(inline, 7, 16) _
    & "', '" & Mid(inline, 23, 10) & "';"

 

[dejfatman]

Thanks, I'll try it out.