Laravel query builder - bulk insert - Ignore columns not found-CodePudding

I am using laravel query builder where I am bulk inserting hundreds of rows. I want to insert the column found and ignore the columns not found in table

Here is my code:

$proposalOpsEvents = DB::table('table1')->where('proposal_id', $proposal->id)->get();

$proposalOpsEvents = $proposalOpsEvents->toArray();

DB::connection('mysql_archive')->table('archive_table1')->insert($proposalOpsEvents);

I get error "Unknown column".

In table 1 new columns are getting added dynamically. I want to ignore the newly added columns when inserting in archive_table1.

For example,

DB::table('archive_table1')->insert([
    'email' => '[email protected]', //email column found - insert
    'phone' => 0, // phone column found - insert
    'address' => 'A'// address column (newly added) not found - ignore
]);

Any solution for these?

CodePudding user response：

Have you tried the insertOrIgnore method?

DB::table('archive_table1')->insertOrIgnore([
    'email' => '[email protected]', //email column found - insert
    'phone' => 0, // phone column found - insert
    'address' => 'A'// address column (newly added) not found - ignore
]);

CodePudding user response：

You can use the DB::squemaBuilder

$columns=DB::connection('your_connection')->getSchemaBuilder()->getColumnListing('table_name');

This will return an array with all columns names of the table and with array manipulation you can intersect your columns to insert with columns in table.

$newData=[
    'email' => '[email protected]', //email column found - insert
    'phone' => 0, // phone column found - insert
    'address' => 'A'// address column (newly added) not found - ignore
]; 

$newDataClean=array_intersect_key($newData, array_flip($columns));

CodePudding user response：

insert into ('foo ') //Are you sure there are no spaces when specifying a field?

//code should be 

insert into ('foo') //removed spaces