How to save multiple rows to multiple database tables at onces

Question

For example i have "id, name, wage, sex, age" columns.

1, John, 3, M, 30
2, Angela, 5, F, 26

If i have 50 rows like this. And if i want to save name, wage into table1 & sex and age into table2. In laravel docs queries/insert, they told us make an array and put values on it. But how should i put some of the values into table1 and other values into table2 in same foreach.

foreach($test as $tests)
   {
    $data[] =[
                'name' => $tests->name,
                'wage' => $tests->wage,
                'sex' => $tests->sex,
                'age' => $tests->age
               ];                 

   }
  Products::insert($data);

Is this the right ways to do it? I cant figure out the correct way to do.

so, how u connect data from table1 to table2, is it any relationship or whatever? — GONG, Jan 15 '18 at 10:49
Possible duplicate of [How to insert multiple rows from a single query using eloquent/fluent](https://stackoverflow.com/questions/29723865/how-to-insert-multiple-rows-from-a-single-query-using-eloquent-fluent) — Abhinav Verma, Jan 15 '18 at 10:49

Alexey Mezenin · Accepted Answer · 2018-01-15T11:29:27.977

If these tables are not related, you can do it with just 2 queries:

foreach ($tests as $test) {
    $products[] = [
        'name' => $tests->name,
        'wage' => $tests->wage
    ];     

    $otherData[] = [
        'sex' => $tests->sex,
        'age' => $tests->age
    ];
}

Products::insert($products);
OtherModel::insert($otherData);

In case if these models are related, you'll need to create 51 query instead of 2 (still better than 100 queries):

foreach ($tests as $test) {
    $productId = Products::insertGetId([
        'name' => $tests->name,
        'wage' => $tests->wage,
    ]);     

    $otherData[] = [
        'sex' => $tests->sex,
        'age' => $tests->age,
        'product_id' => $productId
    ];
}

OtherModel::insert($otherData);

If these models are related and you still want to do this with just a few queries, you could use transactions:

DB::transaction(function () {
    $productId = (int)DB::table('products')->orderBy('id', 'desc')->value('id');
    foreach ($tests as $test) {
        $productId++;

        $products[] = [
            'name' => $tests->name,
            'wage' => $tests->wage
        ];     

        $otherData[] = [
            'sex' => $tests->sex,
            'age' => $tests->age,
            'product_id' => $productId
        ];
    }

    Products::insert($products);
    OtherModel::insert($otherData);
});

score 0 · Answer 2 · answered Jan 15 '18 at 10:51

you could loop trough data and insert into DB table.

foreach($test as $tests)
{
    $product = new Products();
    $product->name = $tests->name;
    $product->name = $tests->name;
    $product->save();

    $another = new AnotherTableModel();
    $another->sex= $tests->sex;
    $another->age= $tests->age;
    $another->save();                       
}

How to save multiple rows to multiple database tables at onces

2 Answers2