Skip to content

Instantly share code, notes, and snippets.

Created May 5, 2017 00:11
Show Gist options
  • Save pauln/884e1a229d439640fbe35e848852fe0b to your computer and use it in GitHub Desktop.
Save pauln/884e1a229d439640fbe35e848852fe0b to your computer and use it in GitHub Desktop.
Simple WordPress bulk insert function

Simple WordPress Bulk Insert

A very simple function to perform bulk SQL inserts, since WPDB doesn't provide one directly. The aim is for simplicity - both in the function itself, and in using it - rather than being a massive beast which covers all possible incorrect usage scenarios. Provide it with clean, consistent data, and it should hopefully be able to do what you want without issue.


  • Provide a table name and an array of associative arrays of rows to insert
  • Column names are pulled from the first row of data automatically
  • Make sure you provide the same fields in each row (there's no protection for this)
  • Data types (for WPDB placeholders) are auto-detected for each individual value (using is_numeric())
  • There is no protection for exceeding maximum query size (i.e. MySQL's max_allowed_packet); you could pre-batch into smaller "safe" chunks if you need to handle this case - or just find a better way to insert such a large amount of data
  • The function returns the number of affected rows (i.e. number of rows inserted)
// Bulk inserts records into a table using WPDB. All rows must contain the same keys.
// Returns number of affected (inserted) rows.
function wpdb_bulk_insert($table, $rows) {
global $wpdb;
// Extract column list from first row of data
$columns = array_keys($rows[0]);
$columnList = '`' . implode('`, `', $columns) . '`';
// Start building SQL, initialise data and placeholder arrays
$sql = "INSERT INTO `$table` ($columnList) VALUES\n";
$placeholders = array();
$data = array();
// Build placeholders for each row, and add values to data array
foreach ($rows as $row) {
$rowPlaceholders = array();
foreach ($row as $key => $value) {
$data[] = $value;
$rowPlaceholders[] = is_numeric($value) ? '%d' : '%s';
$placeholders[] = '(' . implode(', ', $rowPlaceholders) . ')';
// Stitch all rows together
$sql .= implode(",\n", $placeholders);
// Run the query. Returns number of affected rows.
return $wpdb->query($wpdb->prepare($sql, $data));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment