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
 
class NameParser
{

Parsing a full name into components in PHP

string-parsing arrays normalization
Intermediate 8 steps
php 
<?php
 
namespace App\Services\Checkout;
 

Validating coupons with Laravel's Pipeline

pipeline chain of responsibility transactions
Intermediate 7 steps
php 
<?php
 
namespace App\Services;
 

How a password strength validator works in PHP

validation regular-expressions data-driven
Intermediate 8 steps
php 
<?php
 
namespace App\Services;
 

Building a cached daily leaderboard in Laravel

caching aggregation eager-loading
Intermediate 9 steps
php 
<?php
 
final class RotatingFileLogger
{

How a rotating file logger works in PHP

logging file-rotation io
Intermediate 9 steps
php 
class TwoFactorController extends Controller
{
    public function show(Request $request): View|RedirectResponse
    {

Two-factor auth challenge flow in Laravel

authentication two-factor middleware
Intermediate 10 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