10

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?

Improve this question
3
  • 1
    A potential solution, specific to prepared statements, is "single prepare, multiple executions" Commented Jan 11, 2011 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. Commented Jan 11, 2011 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. Commented Jan 11, 2011 at 16:10

2 Answers 2

Reset to default
6

Well, you have three options.

  1. 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.

  2. 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.

  3. 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...

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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: 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...
All values are inserted as PDO::PARAM_STR, aren't they ? How to specify the types ?
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

Improve this answer

5 Comments

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.
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)...
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.
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).
@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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.