Batch insertions and bug fix request

Question

Ok, so I need to populate the MS Access database table with the results from the MySQL query. This is not at all difficult. I have a program written where it copies the template .mdb file into a temporary name and opens it through odbc. There are no problems so far.

I noticed that Access does not support batch insertion ( VALUES (foo, bar), (second, query), (third query)). So this means that I need to execute one query for each row (there are potentially hundreds of thousands of rows). Initial performance tests show a speed of about 900 entries / sec in Access. With our largest datasets, this can mean the execution time of minutes (which is not the end of the world, but obviously, the faster the better).

So, I tried to test the prepared statement. But I keep getting error ( Warning: odbc_execute() [function.odbc-execute]: SQL error: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect , SQL state 07001 in SQLExecute in D:\....php on line 30).

Here is the code I'm using (line 30 - odbc_execute):

$sql = 'INSERT INTO table 
    ([field0], [field1], [field2], [field3], [field4], [field5]) 
    VALUES (?, ?, ?, ?, ?, ?)';
$stmt = odbc_prepare($conn, $sql);
for ($i = 200001; $i < 300001; $i++) {
    $a = array($i, "Field1 $", "Field2 $i", "Field3 $i", "Field4 $i", $i);
    odbc_execute($stmt, $a);
}

So my question is twofold. Firstly, is there any idea on why I am getting this error (I checked, and the number in the array corresponds to the list of fields, which corresponds to the number of parameters ?)? And secondly, should I even bother with this or just use direct INSERT statements? As I said, time is not critical, but if possible, I would like the time to be as low as possible (again, I can limit the bandwidth of the disk, since 900 operations / sec are already high) ..

thank

Answer 1

? ?

MS Access MySQL?

Answer 2

PHP INSERT, ? , VALUES. Jet "Field1 200001" , .

, PHP, "Field1 $i" "Field1 $"?

PHP? ?

INSERT INTO table 
    ([field0], [field1], [field2], [field3], [field4], [field5]) 
VALUES (
    200001,
    'Field1 200001',
    'Field2 200001',
    'Field3 200001',
    'Field4 200001',
    200001);

?

$sql = 'INSERT INTO table 
    ([field0], [field1], [field2], [field3], [field4], [field5]) 
    VALUES (?, "?", "?", "?", "?", ?)';