Mass insert get duplicate and continu

Question

I'm using Laravel and I'm trying to do a massive insert with :

    try {
        foreach ($users as $key => $user) {
            DB::table('users')->insert(
                [
                    'name' => $user->user_name,
                    'email' => $user->user_email,
                ]
            );
        }

    } catch (\Exception $e) {
        //something
    }

So $users is a big array and I do an insert for each element. I have an unique constraint on my field email, so, if I have in my array multiple same emails, I get an error "Duplicate entry" and query just stop.

How can I continu insertion even if I have a duplicate entry ? Can I just ignore them ? (and get them for debug)

Thanks !

Answer 1

You can use try\catch for this and catch the Illuminate\Database\QueryException.

try{
    DB::table('users')->insert(
        [
            'name' => $user->user_name,
            'email' => $user->user_email,
        ]
    );
} catch(Illuminate\Database\QueryException $e) {
    //i want to consume this and continue, so I do nothing here.
}

An error code will be provided through the $e, and 1062 is the Duplicate Key warning. You can specifically look for that inside of your catch block.

catch (Illuminate\Database\QueryException $e){
    $errorCode = $e->errorInfo[1];
    if($errorCode == 1062){
        // houston, we have a duplicate entry problem
    }
}

Credit for the catch functionality here

Answer 2

If you want to continue inserting data without interruption try following

foreach ($users as $key => $user) {
    try {
        DB::table('users')->insert([
            'name' => $user->user_name,
            'email' => $user->user_email,
        ]);
    } catch (\Exception $e) {
        //dump detail here
    }
}