Split an array to make an insertion of 2100 items in a batch?

Question

I have a problem in SQL Server Insertion limit. As I research for the solution, I have 2 ways to solve my problem. The first one is to edit the SQL configuration file (but maybe I'll ask my senior if I can change because clients might be affected if there will be an error on the database side due to wrong SQL configuration that I've made). The second solution is easy but I don't know if I should do it in a loop or there's a way that I can use in Laravel/PHP?

Here's the error that I'm facing:

SQL Server supports a maximum of 2100 parameters.
SQLSTATE[IMSSP]: Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters.

Here's the sample code that I've made:

$userPermissions = [];

$branchPermissionUser = BranchPermissionUser::get()->where('branch_id', $branchIdFrom)->whereIn('user_id', $userIds);

foreach($userIds as $userId) {
    $permissionIds = $branchPermissionUser->where('user_id', $userId)->pluck('permission_id');

    foreach($permissionIds as $permissionId) {
        $userPermissions[] = [
            'user_id' => $userId,
            'branch_id' => $branchIdTo,
            'permission_id' => $permissionId
        ];
    }
}

BranchPermissionUser::insert($userPermissions);

You'll notice that the logic that I've made is first I initialize an empty array of $userPermissions and then each $permissionId of a user is I'll be pushing on the $userPermissions.

There's an instance that this array length ($userPermissions) will be more than 2100. And once the array length is more than 2100, I'm facing the error on SQL Insertion.

To fix this, I'm thinking if there's a way in Laravel/PHP that I can insert it as a By batch of 2100. Is for loop is enough to solve this problem? Or you have any way to solve this problem?

Easiest would be to use a collection instead of an array. It supports the `chunk($callback)` method. — Namoshek, Nov 03 '19 at 07:46
Sounds like you might want to consider using [Table Valued Parameters](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver15) — Dale K, Nov 03 '19 at 07:49
https://stackoverflow.com/questions/51487769/how-to-insert-big-data-on-the-laravel is probably worth a read. — Nigel Ren, Nov 03 '19 at 08:24
Thanks @nigel this is another worth to read. I'll be doing this also to avoid big bulk chunk!!! — Star, Nov 03 '19 at 09:27

score 5 · Accepted Answer · answered Nov 03 '19 at 08:15

5

array array_chunk ( array $input , int $size [, bool $preserve_keys = false ] )

Check this: http://php.net/manual/en/function.array-chunk.php

answered Nov 03 '19 at 08:15

Richard Dobroň

687
4
6

score 2 · Answer 2 · answered Nov 03 '19 at 08:55

Using arrays

foreach (array_chunk($userPermissions, 2100) as $userPermission) {
   BranchPermissionUser::insert($userPermission);
}

Using Laravel Collections chunk()

foreach (collect($userPermissions)->chunk(2100) as $userPermission) {
   BranchPermissionUser::insert($userPermission);
}

Split an array to make an insertion of 2100 items in a batch?

2 Answers2