Bulk Parameterized Inserts

Question

I'm trying to switch some hard-coded queries to use parameterized inputs, but I've run into a problem: How do you format the input for parameterized bulk inserts?

Currently, the code looks like this:

$data_insert = "INSERT INTO my_table (field1, field2, field3) ";
$multiple_inserts = false;
while ($my_condition)
{
    if ($multiple_inserts)
    {
        $data_insert .= " UNION ALL ";
    }

    $data_insert .= " SELECT myvalue1, myvalue2, myvalue3 ";
}

$recordset = sqlsrv_query($my_connection, $data_insert);

A potential solution (modified from How to insert an array into a single MySQL Prepared statement w/ PHP and PDO) appears to be:

$sql = 'INSERT INTO my_table (field1, field2, field3) VALUES ';
$parameters = array();
$data = array();
while ($my_condition)
{
    $parameters[] = '(?, ?, ?)';
    $data[] = value1;
    $data[] = value2;
    $data[] = value3;
}

if (!empty($parameters)) 
{
    $sql .= implode(', ', $parameters);
    $stmt = sqlsrv_prepare($my_connection, $sql, $data);
    sqlsrv_execute($stmt);
}

Is there a better way to accomplish a bulk insert with parameterized queries?

A potential solution, specific to prepared statements, is "single prepare, multiple executions" — Your Common Sense, Jan 11 '11 at 15:38
I was trying to avoid doing that to limit the need for transaction-handling. If any one of the inserts fails, the entire operation should fail. — Zac Howland, Jan 11 '11 at 15:50
If I execute separate statements, I'll have to add a transaction to allow for rolling back should an error occur during the addition of any given row. A bulk insert does not require a transaction since it will either all succeed or all fail. — Zac Howland, Jan 11 '11 at 16:10

score 6 · Accepted Answer · answered Jan 11 '11 at 16:02

Well, you have three options.

Build once - execute multiple. Basically, you prepare the insert once for one row, then loop over the rows executing it. Since the SQLSERVER extension doesn't support re-binding of a query after it's been prepared (you need to do dirty hacks with references) that may not be the best option.
Build once - execute once. Basically, you build one giant insert as you said in your example, bind it once, and execute it. This is a little bit dirty and misses some of the benefits that prepared queries gives. However, due to the requirement of references from Option 1, I'd do this one. I think it's cleaner to build a giant query rather than depend on variable references.

Build multiple - execute multiple. Basically, take the method you're doing, and tweak it to re-prepare the query every so many records. This prevents overly big queries and "batches" the queries. So something like this:

$sql = 'INSERT INTO my_table (field1, field2, field3) VALUES ';
$parameters = array();
$data = array();

$execute = function($params, $data) use ($my_connection, $sql) {
    $query = $sql . implode(', ', $parameters);
    $stmt = sqlsrv_prepare($my_connection, $query, $data);
    sqlsrv_execute($stmt);
}

while ($my_condition) {
    $parameters[] = '(?, ?, ?)';
    $data[] = value1;
    $data[] = value2;
    $data[] = value3;
    if (count($parameters) % 25 == 0) {
        //Flush every 25 records
        $execute($parameters, $data);
        $parameters = array();
        $data = array();
    }
}
if (!empty($parameters))  {
    $execute($sql, $parameters, $data);
}

Either method will suffice. Do what you think fits your requirements best...

Just for clarification: In both Option 2 and 3, the SQL statement would look something like this (if it were to be printed to screen): `INSERT INTO mytable (field1, field2, field3) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?), etc.` to be filled in by the array passed to it. Is that correct? — Zac Howland, Jan 11 '11 at 16:16
@Zac: Yes, that's correct. The 3rd would have at most 25 of the groups (you could tweak that depending on data size). The 2nd would have as many as you have rows... — ircmaxell, Jan 11 '11 at 16:18
All values are inserted as `PDO::PARAM_STR`, aren't they ? How to specify the types ? — Pierre de LESPINAY, Feb 27 '14 at 16:00

score 2 · Answer 2 · answered Jan 11 '11 at 16:33

2

Why not just use "prepare once, execute multiple" method. I know you want it to either all fail or all work, but it's not exactly hard to handle that with transactions:

http://www.php.net/manual/en/pdo.begintransaction.php

http://www.php.net/manual/en/pdo.commit.php

http://www.php.net/manual/en/pdo.rollback.php

answered Jan 11 '11 at 16:33

Stephen

18,597
4
32
33

Its not difficult, but it adds a level of complexity to this code that isn't really needed. Bulk Inserts are the most elegant solution to my problem, its just a matter of syntax that I wasn't sure of. – Zac Howland Jan 11 '11 at 16:40
Plus this is using the native `sqlsrv` extension which isn't the sanest thing in the world (if he was using PDO, possibly, but the native sqlsrv extension is lacking in quite a few areas)... – ircmaxell Jan 11 '11 at 16:46
1

if the point of this is to move to paramaterised queries, why not move to PDO at the same time? as for the "complexity" it's not exactly hard to add a check if a call to execute() fails, and if so do a rollback() and then any other error handling you need. – Stephen Jan 11 '11 at 16:57
Because PDO no longer supports using the SQL Server (TDS) drivers and creating a special case for connecting to this database is not a viable option (there is a global connection string already established that works with both the mssql_ and sqlsrv_ extensions, but not with PDO using ODBC). – Zac Howland Jan 11 '11 at 17:01
@ZacHowland I would not say the most elegant solution, I would maybe say the easiest. For me _Prepare once / Execute multiple_ is the most elegant. – Pierre de LESPINAY Feb 27 '14 at 16:12

Bulk Parameterized Inserts

2 Answers2

Linked