Bulk insert in Laravel using eloquent ORM

Question

How can we do bulk database inserts in Laravel using Eloquent ORM?

I want to do this in Laravel: https://stackoverflow.com/a/2129441/2326321 but I get the following error.

SQLSTATE [HY093]: Invalid parameter number: mixed names and positional parameters.

Answer 1

You can simply use Eloquent::insert() .

For example:

 $data = array( array('name'=>'Coder 1', 'rep'=>'4096'), array('name'=>'Coder 2', 'rep'=>'2048'), //... ); Coder::insert($data); 

Answer 2

We can easily update GTF response to update timestamps.

 $data = array( array( 'name'=>'Coder 1', 'rep'=>'4096', 'created_at'=>date('Ymd H:i:s'), 'modified_at'=> date('Ymd H:i:s') ), array( 'name'=>'Coder 2', 'rep'=>'2048', 'created_at'=>date('Ymd H:i:s'), 'modified_at'=> date('Ymd H:i:s') ), //... ); Coder::insert($data); 

Update: to simplify the date, we can use carbon as suggested by @Pedro Moreira

 $now = Carbon::now('utc')->toDateTimeString(); $data = array( array( 'name'=>'Coder 1', 'rep'=>'4096', 'created_at'=> $now, 'modified_at'=> $now ), array( 'name'=>'Coder 2', 'rep'=>'2048', 'created_at'=> $now, 'modified_at'=> $now ), //... ); Coder::insert($data); 

UPDATE2: for laravel 5 use updated_at instead of modified_at

 $now = Carbon::now('utc')->toDateTimeString(); $data = array( array( 'name'=>'Coder 1', 'rep'=>'4096', 'created_at'=> $now, 'updated_at'=> $now ), array( 'name'=>'Coder 2', 'rep'=>'2048', 'created_at'=> $now, 'updated_at'=> $now ), //... ); Coder::insert($data); 

Answer 3

For anyone reading this, check out the createMany() method .

 /** * Create a Collection of new instances of the related model. * * @param array $records * @return \Illuminate\Database\Eloquent\Collection */ public function createMany(array $records) { $instances = $this->related->newCollection(); foreach ($records as $record) { $instances->push($this->create($record)); } return $instances; } 

Answer 4

This is how you do it more eloquently,

  $allintests = []; foreach($intersts as $item){ //$intersts array contains input data $intestcat = new User_Category(); $intestcat->memberid = $item->memberid; $intestcat->catid= $item->catid; $allintests[] = $intestcat->attributesToArray(); } User_Category::insert($allintests); 

Answer 5

I searched many times for it, finally used custom timestamps , as shown below:

 $now = Carbon::now()->toDateTimeString(); Model::insert([ ['name'=>'Foo', 'created_at'=>$now, 'updated_at'=>$now], ['name'=>'Bar', 'created_at'=>$now, 'updated_at'=>$now], ['name'=>'Baz', 'created_at'=>$now, 'updated_at'=>$now], .................................. ]); 

Answer 6

Eloquent::insert is the right solution, but it will not update the timestamps, so you can do something like below

  $json_array=array_map(function ($a) { return array_merge($a,['created_at'=> Carbon::now(),'updated_at'=> Carbon::now()] ); }, $json_array); Model::insert($json_array); 

The idea is to add create_at and updated_at to the whole array before performing the insert

Answer 7

Or when using Laravel Factories (creating 10 users):

 $users = factory(App\User::class, 10)->make()->toArray(); App\User::insert($users); 

Answer 8

To insert category relationships, I ran into the same problem and had no idea, except that in my eloquent model, I used Self () to have an instance of the same class in foreach to write multiple save and capture identifiers.

 foreach($arCategories as $v) { if($v>0){ $obj = new Self(); // this is to have new instance of own $obj->page_id = $page_id; $obj->category_id = $v; $obj->save(); } } 

without "$ obj = new Self ()" it only saves one record (when $ obj was $ this)

Answer 9

 $start_date = date('Ymd h:m:s'); $end_date = date('Ymd h:m:s', strtotime($start_date . "+".$userSubscription['duration']." months") ); $user_subscription_array = array( array( 'user_id' => $request->input('user_id'), 'user_subscription_plan_id' => $request->input('subscription_plan_id'), 'name' => $userSubscription['name'], 'description' => $userSubscription['description'], 'duration' => $userSubscription['duration'], 'start_datetime' => $start_date, 'end_datetime' => $end_date, 'amount' => $userSubscription['amount'], 'invoice_id' => '', 'transection_datetime' => '', 'created_by' => '1', 'status_id' => '1', ), array( 'user_id' => $request->input('user_id'), 'user_subscription_plan_id' => $request->input('subscription_plan_id'), 'name' => $userSubscription['name'], 'description' => $userSubscription['description'], 'duration' => $userSubscription['duration'], 'start_datetime' => $start_date, 'end_datetime' => $end_date, 'amount' => $userSubscription['amount'], 'invoice_id' => '', 'transection_datetime' => '', 'created_by' => '1', 'status_id' => '1', ) ); dd(UserSubscription::insert($user_subscription_array)); 

UserSubscription is my model name. This will return true if successfully inserted else else false.

Answer 10

Perhaps a more Laravel way to solve this problem is to use a collection and a loop that it inserts using a model using timestamps.

 <?php use App\Continent; use Illuminate\Database\Seeder; class InitialSeeder extends Seeder { /** * Run the database seeds. * * @return void */ public function run() { collect([ ['name' => 'América'], ['name' => 'África'], ['name' => 'Europa'], ['name' => 'Asia'], ['name' => 'Oceanía'], ])->each(function ($item, $key) { Continent::forceCreate($item); }); } } 

EDIT:

Sorry for my misunderstanding. For bulk insertion, this can help, and perhaps with this you can make good seeders and optimize them a bit.

 <?php use App\Continent; use Carbon\Carbon; use Illuminate\Database\Seeder; class InitialSeeder extends Seeder { /** * Run the database seeds. * * @return void */ public function run() { $timestamp = Carbon::now(); $password = bcrypt('secret'); $continents = [ [ 'name' => 'América' 'password' => $password, 'created_at' => $timestamp, 'updated_at' => $timestamp, ], [ 'name' => 'África' 'password' => $password, 'created_at' => $timestamp, 'updated_at' => $timestamp, ], [ 'name' => 'Europa' 'password' => $password, 'created_at' => $timestamp, 'updated_at' => $timestamp, ], [ 'name' => 'Asia' 'password' => $password, 'created_at' => $timestamp, 'updated_at' => $timestamp, ], [ 'name' => 'Oceanía' 'password' => $password, 'created_at' => $timestamp, 'updated_at' => $timestamp, ], ]; Continent::insert($continents); } } 

Answer 11

SOLVED PROBLEM ............. CHANGE TABLES FOR MIGRATION ...

 $table->timestamp('created_at')->nullable()->useCurrent(); 

EASILY

 Schema::create('spider_news', function (Blueprint $table) { $table->bigIncrements('id'); $table->string('source')->nullable(); $table->string('title')->nullable(); $table->string('description')->nullable(); $table->string('daterss')->nullable(); $table->timestamp('created_at')->nullable()->useCurrent(); $table->timestamp('update_at')->nullable()->useCurrent(); });