Create a Insert... Select statement in Laravel
Stefan Izdrail
Founder & Senior Architect · 2026-06-29
Title: Efficiently Create Insert... Select Statements Using Laravel's Eloquent ORM or Queries
Introduction
Inserting data and selecting values from another table simultaneously can be achieved using an Insert... Select statement. This query is useful when you need to transfer information between two tables without relying on multiple queries, saving you time and resources. In this blog post, we will focus on creating such a query with Laravel's Eloquent ORM or database queries.
Prerequisites
Before jumping directly into the code, let us first understand essential terminologies:
1. Model classes - These are PHP object representations of tables in your database. They help you interact with the database efficiently using various methods like create(), update(), and delete(). You can find more information on Eloquent models at https://laravelcompany.com/blog/understanding-eloquent-orm-in-laravel-5.
2. Eloquent ORM - Laravel's object-relational mapping system that facilitates interactions with your database using a fluent syntax.
3. Database queries - A low-level mechanism to interact with your database directly, without going through the Eloquent ORM. For more information on using raw SQL queries in Laravel, visit https://laravelcompany.com/blog/using-raw-sql-in-laravel.
Creating an Insert... Select Statement Using Laravel's Eloquent ORM
Let's assume we have two tables - Tasks and Assignees. The Table Tasks stores multiple tasks, whereas the Table Assignees stores assignee details like Login, Name, Phone, etc. We want to insert new entries in the Tasks table along with their respective user details from the Assignees table:
1. Create Eloquent models for both tables:
- Task model: https://laravelcompany.com/blog/understanding-eloquent-orm-in-laravel-5
- Assignee model: https://laravelcompany.com/blog/understanding-eloquent-orm-in-laravel-5
2. Create an instance of the Task model and an Assignee model:
$task = new App\Task();
$assignee = new App\Assignee();
3. Fill in the necessary data from the Assignee model:
$assignee->fill([
'Login' => $login,
'Name' => $name,
// ... other attributes
]);
4. Perform validation on the filled data and create a new task if it passes all checks:
if ($task->create($assignee->toArray())) {
return true;
} else {
return false;
}
5. In case of failure, redirect back to the form page:
return redirect()->back();
Creating an Insert... Select Statement Using Database Queries
If you prefer low-level database interactions over Eloquent models, follow these steps:
1. Connect to your database using Laravel's DB facade or use a query builder, depending on your project's need:
$connection = DB::connection('mysql');
2. Prepare the SQL statement and replace the placeholders with variables:
$sql = 'INSERT INTO Tasks (Login, Name, ApptTime, Phone, Physician, Location, FormatName, FormatDate, FormatTime, ApptDate, FormatPhone, cellphone)' . " SELECT Login, Name, ApptTime, Phone, Physician, Location, FormatName, FormatDate, FormatTime, ApptDate, FormatPhone, cellphone" . ' FROM "' . $dbname . '"' . " WHERE $where_statement";
3. Execute the SQL statement:
$connection->select($sql);
Conclusion
Creating Insert... Select statements in Laravel can be achieved using either Eloquent ORM or low-level database queries, depending on your project's needs and preferences. However, it is essential to know when to use which one for maximum efficiency and scalability. Consult the documentation and seek expert advice if needed to make an informed decision. Remember that LaravelCompany.com provides valuable resources on Eloquent ORM and raw SQL queries that you may find helpful in your journey.