How to insert multiple rows from a single query using eloquent/fluent

How to insert multiple rows from a single query using eloquent/fluent

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Batching Inserts with Eloquent: Moving from Query Results to Database Rows

You have a query that fetches user_id and subject_id for the current user, returning a collection of models or an array. The goal is to insert these records into a pivot table (or similar many-to-many mapping) without manually looping through every single row. While iterating with foreach works perfectly fine for small datasets, it introduces a significant performance bottleneck as the dataset grows. Each iteration triggers a separate SQL statement, forcing the database to parse and execute the query repeatedly.

The most efficient approach in Laravel is to leverage Eloquent's batch insert capabilities or raw SQL execution within the collection context. This allows you to push the entire dataset into a single INSERT statement (or a series of optimized statements) under the hood.

Using insertOrIgnore with Collections

If your target table uses primary keys that might already exist, or if you want to handle duplicates gracefully, Eloquent provides insertOrIgnore. You can pass the entire collection directly to this method. This is often cleaner than raw SQL because it handles model casting and mass assignment protection automatically.

Here is how you would structure the insertion:

$subjects = UserSubject::where('user_id', Auth::id())
    ->select('subject_id')
    ->get();

// Prepare data for insertion
$records = $subjects->map(function ($model) {
    return [
        'user_id' => $model->user_id,
        'subject_id' => $model->subject_id,
        // Add other columns if necessary
    ];
})->toArray();

// Perform the batch insert
UserSubject::insertOrIgnore($records);

In this scenario, Eloquent transforms the collection into a single array of arrays and executes an optimized INSERT command. The insertOrIgnore method specifically targets the unique constraint on the primary key (or composite key) to prevent duplicate entries without throwing exceptions. This is particularly useful when refreshing data where some rows might already exist.

Handling Raw Inserts for Maximum Control

Sometimes you need more control over the SQL generation, perhaps to include specific columns that aren't model attributes or to handle a very large volume of data where even batch inserts might feel heavy. In those cases, converting the collection to an array and using insert on the model class is the standard pattern.

$records = UserSubject::where('user_id', Auth::id())
    ->select('subject_id')
    ->get()
    ->map(function ($model) {
        return [
            'user_id' => $model->user_id,
            'subject_id' => $model->subject_id,
        ];
    })->toArray();

UserSubject::insert($records);

The key here is the ->toArray() call. Eloquent expects an array of arrays for mass insertion. If you pass a collection directly without converting it first, Laravel might not recognize the batch context correctly depending on the version and specific method used. Converting to an array ensures the query builder receives the raw data structure required for the INSERT statement.

Performance Considerations and Trade-offs

The primary advantage of this approach over a simple foreach loop is database I/O reduction. A loop creates $N$ network round-trips and parse cycles, whereas a batch insert creates one. However, there are limitations to keep in mind. If the dataset is massive (thousands of rows), memory usage for holding the entire collection before insertion could become a factor. In such high-volume scenarios, chunking the query results or using database-level stored procedures might be necessary.

Additionally, ensure that your target table has appropriate unique constraints defined. Methods like insertOrIgnore rely on these constraints to determine which rows to skip or update. If you are inserting into a new table where IDs are auto-incremented (like the ID column in your desired output), Laravel will handle generating those values automatically during the insert, so you do not need to specify them in the array unless you are manually managing them.

For complex many-to-many relationships, always verify that the user_id exists before attempting the insert to maintain data integrity. If this logic is part of a larger application flow, consider checking if the relationship already exists via the pivot table before executing the insert to avoid unnecessary writes.

Our network