insert(), insertOrIgnore(), upsert() — batch inserts

Intermediate5 min read·lv-13-011

sqlperformance

Concept

Insert methods offer varying levels of performance and conflict handling. The right choice depends on whether you need duplicate handling, model events, or maximum throughput.

insert(array $data): Single or batch INSERT. No model events. No timestamps auto-set. For batch inserts, pass an array of arrays.

insertOrIgnore(array $data): INSERT IGNORE (MySQL) / INSERT OR IGNORE (SQLite). Skips rows that violate unique constraints silently. Returns the number of affected rows.

upsert(array $data, array $uniqueKeys, array $updateColumns): INSERT ... ON DUPLICATE KEY UPDATE (MySQL) / INSERT OR REPLACE (SQLite). Inserts new rows; updates specified columns for existing rows that match $uniqueKeys. The updateColumns parameter is required — empty array would cause errors on some drivers.

insertGetId(array $data): Insert a single row and return the auto-increment ID.

insertUsing(array $columns, Builder|Closure|string $query): INSERT INTO ... SELECT .... Efficient for copying/transforming data between tables without PHP round-trips.

When NOT to use Query Builder inserts:

When model events (creating, created) must fire → use Eloquent.
When $fillable / $casts / accessors must apply → use Eloquent.
When auto-timestamps must be set → pass now() manually or use Eloquent.

Timestamps manual handling: Query Builder doesn't know about created_at / updated_at. Always include them in batch inserts.

Code Example

php

<?php
use Illuminate\Support\Facades\DB;

// Single insert
DB::table('users')->insert([
    'name'       => 'Alice',
    'email'      => 'alice@example.com',
    'created_at' => now(),
    'updated_at' => now(),
]);

// Batch insert — single SQL statement, much faster than N individual inserts
$rows = collect(range(1, 1000))->map(fn($i) => [
    'name'       => "User $i",
    'email'      => "user$i@example.com",
    'created_at' => now(),
    'updated_at' => now(),
])->all();

DB::table('users')->insert($rows); // INSERT INTO users (...) VALUES (...), (...), ...

// insertGetId — get the new auto-increment ID
$id = DB::table('users')->insertGetId([
    'name'       => 'Bob',
    'email'      => 'bob@example.com',
    'created_at' => now(),
    'updated_at' => now(),
]);

// insertOrIgnore — skip duplicate email violations
DB::table('users')->insertOrIgnore([
    ['email' => 'existing@email.com', 'name' => 'Alice'],  // skipped
    ['email' => 'new@email.com',      'name' => 'Bob'],    // inserted
]);

// upsert — insert or update on conflict
DB::table('user_preferences')->upsert(
    [
        ['user_id' => 1, 'key' => 'theme', 'value' => 'dark', 'updated_at' => now()],
        ['user_id' => 1, 'key' => 'lang',  'value' => 'en',   'updated_at' => now()],
    ],
    ['user_id', 'key'],      // unique key combination to check
    ['value', 'updated_at']  // columns to update when key exists
);

// insertUsing — INSERT SELECT (no PHP loop needed)
DB::table('order_archive')->insertUsing(
    ['user_id', 'total', 'status', 'archived_at'],
    DB::table('orders')
        ->select('user_id', 'total', 'status', DB::raw('NOW()'))
        ->where('created_at', '<', now()->subYear())
);

PreviousAggregates — count(), max(), min(), avg(), sum()Query Builder Nextupdate(), updateOrInsert(), increment(), decrement()Query Builder