Insert from select in laravel using eloquent [duplicate]

Question

I am using laravel 8. I want to insert multiple rows by selecting from one table to another. I am using DB::statement() but now want to do it using eloquent. Here is my sample code:

DB::statement("insert into bank_information (user_id, bank_id, account_name,account_number)
        select applicant_id,bank_id,account_name,account_number from applicant_bank_information
        where erec_applicant_bank_information.applicant_id =".$applicant_id);

How can I do it using single eloquent command ? Also, Is there any other smarter/faster way for it in laravel ??

Stefan Pavlov · Accepted Answer · 2021-05-28 09:06:23Z

6

I do not know of a Eloquent way for this, but after Laravel 5.7.17 we have a new method in the query builder insertUsing(). Unfortunately this is not mentioned in the official documentation.

The query builder code will be something like this:

$select = DB::table('applicant_bank_information as abi')
    ->select('applicant_id','bank_id','account_name','account_number')
    ->where('abi.applicant_id', $applicant_id);
    
$rows_inserted = DB::table('bank_information')
    ->insertUsing(['user_id','bank_id', 'account_name','account_number'], $select);

Tested and working. Keep in mind that you do not run the select request with ->get().

edited May 28, 2021 at 9:06

answered May 27, 2021 at 15:32

Stefan Pavlov

5185 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

miken32 Over a year ago

Note that every column in the target table has to be accounted for, including primary keys. This may involve doing like ->selectRaw('null as id')->addSelect(...) on the source query.

Collectives™ on Stack Overflow

Insert from select in laravel using eloquent [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related