php 29 lines · 6 steps

Bulk-inserting users with batched PDO upserts

Chunk a large array into batches and upsert each with a single prepared multi-row INSERT.

Explained by highlit
Intermediate batching bulk-insert prepared-statements upsert database
1public function importUsers(array $users): int
2{
3 $inserted = 0;
4 
5 foreach (array_chunk($users, 500) as $batch) {
6 $placeholders = [];
7 $values = [];
8 
9 foreach ($batch as $user) {
10 $placeholders[] = '(?, ?, ?)';
11 $values[] = $user['name'];
12 $values[] = $user['email'];
13 $values[] = $user['created_at'] ?? date('Y-m-d H:i:s');
14 }
15 
16 $sql = sprintf(
17 'INSERT INTO users (name, email, created_at) VALUES %s '
18 . 'ON DUPLICATE KEY UPDATE name = VALUES(name)',
19 implode(', ', $placeholders)
20 );
21 
22 $stmt = $this->pdo->prepare($sql);
23 $stmt->execute($values);
24 
25 $inserted += $stmt->rowCount();
26 }
27 
28 return $inserted;
29}
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1Chunking huge datasets keeps each SQL statement within safe placeholder and packet limits.
  2. 2A single multi-row INSERT is far faster than one query per record.
  3. 3ON DUPLICATE KEY UPDATE turns an insert into an idempotent upsert against unique keys.

Related explainers

php 
<?php
 
namespace App\Events;
 

A priority event dispatcher in PHP

observer-pattern callbacks priority-ordering
Intermediate 8 steps
php 
<?php
 
namespace App\Http\Controllers;
 

Verifying Stripe webhooks in Laravel

webhooks signature-verification event-dispatch
Intermediate 8 steps
php 
<?php
 
namespace App\Http\Controllers;
 

Building a filtered product index in Laravel

query-builder validation conditional-queries
Intermediate 9 steps
php 
<?php
 
namespace App\Models\Scopes;
 

How a tenant global scope works in Laravel

multi-tenancy global-scope query-builder
Intermediate 5 steps
php 
<?php
 
namespace App\Support;
 

Retry with exponential backoff in PHP

retry exponential-backoff error-handling
Intermediate 7 steps
python 
from itertools import islice
from typing import Iterable, Iterator, TypeVar
 
T = TypeVar("T")

Batching an iterable for bulk indexing

generators batching lazy-evaluation
Intermediate 7 steps

Share this explainer

Here's the card — post it anywhere.

Bulk-inserting users with batched PDO upserts — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code