Bulk Parameterized Inserts
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...
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