Create a Insert... Select Statement in Laravel

Create a Insert... Select statement in Laravel

There is no way of doing this in one query (unless you are on Laravel 5.7), however I came across the same issue and wanted to make sure I can keep using a certain select I build with the QueryBuilder.

So what you could do, to keep things half what clean and to reuse functionality which has built a select statement before, is this:

/**
 * Wherever your Select may come from
 **/
$select = User::where(...)
                  ->where(...)
                  ->whereIn(...)
                  ->select(array('email','moneyOwing'));
/**
 * get the binding parameters
 **/ 
$bindings = $select->getBindings();
/**
 * now go down to the "Network Layer"
 * and do a hard coded select
 */
 $insertQuery = 'INSERT into user_debt_collection (email,dinero) '
                . $select->toSql();
    
 \DB::insert($insertQuery, $bindings);

UPDATE Laravel 5.7

As of Laravel 5.7.17 you can use ->insertUsing(). See here for details. Thank you @Soulriser for pointing this out.

So above query would look like this:

DB::table('user_debt_collection')->insertUsing(['email','dinero'], $select);

Laravel eloquent insert into select

// First Create Model Parcel and FInd Your required row with id

// Below code use for selecting data from database. 

$parcel = Parcel::query()->where('database column name', '=', $parcel_id)->first()

// If you want to store data in table use below code.

$parcel = new Parcel();
$percel->items = $item;
$percel->status = $newStatus;
$percel->save();

How do I insert into MySql database with select statement using Laravel 7 from two different connections?

Subqueries such as those used by insertUsing won't be able to use a separate database. There's no underlying limitation, it's just that Laravel doesn't use database prefixes when building queries. So if your databases are on the same server you could do this by building a raw query like this:

DB::insert("INSERT INTO mysql1.users SELECT * FROM mysql2.users");

Another option is pulling the data from one DB and inserting it into the other. Assuming you have a fairly large table, you should use chunking to ensure you don't load the entire table into memory.

If you are moving from the "mysql2" connection to your default connection it might look like this:

$users1 = DB::table('users');
$users2 = DB::connection('mysql2')->table('users');
$page = 0;
$size = 50;
while (true) {
    // get some records
    $users = $users2->skip($page * $size)->take($size)->get();
    // end the loop when there are no more results
    if ($users->count() === 0) {
        break;
    }
    // convert the results and each row to an array
    $user_array = $users->map(fn ($u) => (array)$u)->toArray();
    // save them into the new database
    $users1->insert($user_array);
    // increment the page for the next iteration
    $page++;
}

I'd recommend using proper tools such as mysqldump to do this though.

Laravel 5.2 Insert from select using dynamic content

All you need is to use simple insertions into the table, something like this:

foreach ($projectbids as $bid) {
$result = SavedEstimation::create(array(
'project_id' => $bid->project_id,
'provider_id'=> $bid->provider_id,
'user_id' => $bid->user_id,
'estimated_price' => $bid->estimated_price
));
}

There is no way to insert this by using just one query anyway. You could simply store output HTML if the only reason you want to do this is to show same HTML to the user later, but this is bad practice.

---

Related Topics