Level 47
4 likes
Aug 28, 2016
18
Level 14
Insert multiple records in one query
Hello again, :)
I have a list of objects ( same object ) and I would like to insert them in the database at once. I have tried with createMany but this method I think ( I haven't investigated ) calls create for each array which results in a query to the database for each object. I would like to query the database something like this:
INSERT INTO example
(example_id, name, value, other_value)
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4')
Any way I can do this with Eloquent? I haven't found anything yet.
Thanks
Level 11
I guess it'is not in the docs, but every model has an insert() method that accepts an array of attributes.
Example:
\App\Example::insert([
[
'name' => 'abc',
'value' => '123',
],
[
'name' => 'def',
'value' => '456',
],
// etc...
]);
Note: it calls query builder's insert, I think timestamps are not generated (at least last time i've used it).
6 likes
Level 14
this insert runs a wierd query or is it just me ? it looks something like :
insert into table (....) select ? as name ...union all.. select ? ...
Level 14
One more thing.. I should probably use DB facade instead of model shouldn.t I ? :)
Level 11
The call to insert is passed to an instance of Query Builder, the only difference is that the model set up the table name for you (an messes around with scopes, you can see it in Model::newQuery().
This are equivalents pretty much:
\App\Example::insert([/*...*/]);
DB::table('examples')->insert([/*...*/]);
I would stick with the model calls for readability and conveniences (if you change the table you need to update only the model for example, it'll probably never happen but it's an example.)
1 like
Level 1
I've bumped into an issue with the insert method. I'm trying to import a little over 8000 records into a table, but this method is taking much longer - even cancelled it after 5 minutes, only to find there was nothing in the table. For reference, I'm using SQLite, and I chose to use this method in the hopes that it would be faster... Any ideas?
Level 1
Okay, it finally did its thing after about 7 minutes, and bam:
General error: 1 too many SQL variables
Any way I can split it up into, say, chunks of 200?
Level 1
Nope, even limiting it to 10 in total doesn't work... Here's the code I'm using (trimmed):
$records = [];
$results->each(function ($item, $key) use (&$records) {
$record = [
'field' => 'value',
// ...
];
// ... Calculated additions to $record ...
$records[] = $record;
});
Record::insert($records);
Level 1
@mrockett is there a way to create many via many for example Record::insert($records); and records has child and you wanna create records of this child in the same time !
Level 1
Will have to stick to one record at a time anyway - appears to be faster, which is the end-goal anyway.
Level 1
insert
Level 1
@THENODI - you try to add "created_at" to fillable array in Model ?
Level 18
Is there a reason for my code insert just the first item on the array?
MovementStep::insert(
[
'name' => 'Em espera',
'color' => '#999999',
'order' => 0,
'default' => true,
'created_at' => now(),
'updated_at' => now(),
],
[
'name' => 'Em andamento',
'color' => '#FCC400',
'order' => 1,
'default' => true,
'created_at' => now(),
'updated_at' => now(),
],
[
'name' => 'Concluido',
'color' => '#68BC00',
'order' => 2,
'default' => true,
'created_at' => now(),
'updated_at' => now(),
]
);
Level 1
@spacedog4 just add all your arrays inside an array like this : [ [ 'name' => 'Em espera', 'color' => '#999999', 'order' => 0, 'default' => true, 'created_at' => now(), 'updated_at' => now(), ], [ 'name' => 'Em andamento', 'color' => '#FCC400', 'order' => 1, 'default' => true, 'created_at' => now(), 'updated_at' => now(), ] ]
Level 1
Wrap your array of data with array:
MovementStep::insert(**[**
[
'name' => 'Em espera',
'color' => '#999999',
'order' => 0,
'default' => true,
'created_at' => now(),
'updated_at' => now(),
],
[
'name' => 'Em andamento',
'color' => '#FCC400',
'order' => 1,
'default' => true,
'created_at' => now(),
'updated_at' => now(),
],
[
'name' => 'Concluido',
'color' => '#68BC00',
'order' => 2,
'default' => true,
'created_at' => now(),
'updated_at' => now(),
]
**]**);
I hope it will solved the problem.
Level 1
Here is the valuable answer for this question:
https://codingdriver.com/how-to-insert-multiple-rows-in-database-laravel-example.html
Using the databse seeder we can insert multiple records, we can use laravel faker or factory class as well.
Level 1
@codingdriver Seeders are used for creating data on development environments. A seeder can't be used operationally in production code.
Level 1
you should use bulk data upload using csv files
Please or to participate in this conversation.