Bulk Insert in Laravel 5.3

Question

$list = [];

foreach($RoleDetails["Data"]["Permissions"] as $Permission) {
    $MyModel = new UserRolePermissionModel();
    $MyModel->UserID                  = $User->UserID;
    $MyModel->RolePermissionID        = $Permission->RolePermissionID;
    $MyModel->IsActive                = $Permission->IsActive;

    array_push($list, $MyModel);
}
\DB::table('tbluserrolepermission')->insert($list); 

Below are the error details

QueryException {#293 ▼
  #sql: "insert into `tbluserrolepermission` (`0`, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `11`, `12`, `13`, `14`, `15`, `16`, `17`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
  #bindings: array:18 [▶]
  #message: "SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list' (SQL: insert into `tbluserrolepermission` (`0`, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `11`, `12`, `13`, `14`, `15`, `16`, `17`) values ({"UserID":21,"RolePermissionID":19,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":20,"IsActive":0,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":21,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":22,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":23,"IsActive":0,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":24,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":25,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":26,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":27,"IsActive":0,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":28,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":29,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":30,"IsActive":0,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":31,"IsActive":0,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":32,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":33,"IsActive":0,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":34,"IsActive":1,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}, {"UserID":21,"RolePermissionID":35,"IsActive":1,"IsProtectionAvailable":0,"IsProtectionReadOnly":1}, {"UserID":21,"RolePermissionID":36,"IsActive":1,"IsProtectionAvailable":1,"IsProtectionReadOnly":0}))"
  #code: "42S22"
  #file: "C:\xampp\htdocs\AS4\vendor\laravel\framework\src\Illuminate\Database\Connection.php"
  #line: 761
  -previous: PDOException {#356 ▶}
  +errorInfo: array:3 [▶]
  +"previous": PDOException {#356 ▶}
  -trace: {▶}
}

Answer 1

It is not working, because you are not providing an array of arrays with only the values matching the column names to populate the database with.

Example

Lets say you want to populate an array of users and save them to the database. The following example would do that.

DB::table('users')->insert([  
    ['email' => '[email protected]', 'votes' => 0],
    ['email' => '[email protected]', 'votes' => 0]
]);

Note that email and votes in the above example are 2 columns in the users table. Note also that only an array of other arrays are passed to the insert method. What you are trying to insert is actually an array of eloquent model objects.

Source: https://laravel.com/docs/5.3/queries#inserts

Answer 2

I fixed it like below.

foreach($RoleDetails["Data"]["RolePermissions"] as $RolePermission) {
    $data = [
        'UserID'                => $User->UserID,
        'RolePermissionID'      => $RolePermission->RolePermissionID,
        'IsActive'              => $RolePermission->IsActive,
        'IsProtectionAvailable' => $RolePermission->IsProtectionAvailable,
        'IsProtectionReadOnly'  => $RolePermission->IsProtectionReadOnly
    ];
    array_push($list,$data);
}
\DB::table('tbluserrolepermission')->insert($list); 

or it could be like this.

UserRolePermissionModel::insert($list);