How can I prepare a database statement and execute it many times with different input? I have a situation where I need to update a Drupal module's query to run a SELECT query [and based on the results] an INSERT for a data set of ~a million items. 

Outside of Drupal I would accomplish this by first preparing my SELECT and INSERT statements, then executing them once for each item:

$db = new PDO(...);
$check = $db-&gt;prepare('SELECT COUNT(*) FROM other_tablename WHERE colname = :value');
$insert = $db-&gt;prepare('INSERT INTO tablename (colname) VALUES (:value)');
foreach ($my_many_items as $item) {
  $check-&gt;execute(array(':value' =&gt; $item));
  if ($check-&gt;fetchColumn() &lt; 1) {
    $insert-&gt;execute(array(':value' =&gt; $item));
  }
}


With the Drupal 7 DB-API however, there seems to be no way to get a prepared statement without executing it first using db_query($query, array(':value' =&gt; $item), array('return' =&gt; Database::RETURN_STATEMENT)).

The only option I've found is to run db_query() / db_select() / db_insert() in a loop for each item which ends up requiring the query string to be re-parsed for every single iteration.

How can I prepare a database statement (with Drupal doing its table-name rewriting) and then execute it many times with different input?

Background:

The existing implementation uses db_select() to build a query using a giant SELECT ... IN clause, filters the items in PHP, then uses db_insert() to save all of the values at once. While this implementation only runs two queries, it unfortunately blows up the memory usage when handling large numbers of items:

...
$result = db_select('other_tablename', 'o')
  -&gt;fields('o', array('colname'))
  -&gt;condition('colname', array($my_many_items),'IN')
  -&gt;execute()
  -&gt;fetchAll();
$existing = array();
foreach ($result as $r) {
  $existing[] = $r-&gt;colname;
}
$insert = db_insert('tablename')-&gt;fields(array('colname'));
foreach ($my_many_items as $item) {
  if (!in_array($item, $existing)) {
    $insert-&gt;values(array('colname' =&gt; $value));
  }
}
$insert-&gt;execute();
Prepared statements can be created after changing the statement class to PDOStatement. This technique is described in How to execute stored procedures in drupal?

// Get the Drupal database connection and change the statement class to PDOStatement.
// Save the current class for cleanup later.
$conn = Database::getConnection();
$saved_class = $conn-&gt;getAttribute(PDO::ATTR_STATEMENT_CLASS);
$conn-&gt;setAttribute(PDO::ATTR_STATEMENT_CLASS, array('PDOStatement'));

// code from the question:
$check = $conn-&gt;prepare('SELECT COUNT(*) FROM other_tablename WHERE colname = :value');
$insert = $conn-&gt;prepare('INSERT INTO tablename (colname) VALUES (:value)');
foreach ($my_many_items as $item) {
  $check-&gt;execute(array(':value' =&gt; $item));
  if ($check-&gt;fetchColumn() &lt; 1) {
    $insert-&gt;execute(array(':value' =&gt; $item));
  }
}

// Revert the connection to its previous statement class.
$conn-&gt;setAttribute(PDO::ATTR_STATEMENT_CLASS, $saved_class);