How do I insert into MySql database with select statement using Laravel 7 from two different connect-CodePudding

I am trying to copy the data from one database to another database on a different server. The databases are identical in structure. From searching around I have tried using the insertUsing() method in Laravel.

$new_connection = DB::connection('mysql2');

//Insert Using requires an array of columns
$column_list = Schema::getColumnListing('users');

//This is my select statement
$select = $new_connection->table('users')->select('*');

//This is my insert statement
DB::table('users')->insertUsing($column_list,$select);

This doesn't produce any errors but no data is being inserted into the table. If I have identical data in the database I am copying to, I get an error saying the primary key already exists so I know it is reading the data from the other server.

Is there another way of getting this done?

CodePudding user response：

Subqueries such as those used by insertUsing won't be able to use a separate database. There's no underlying limitation, it's just that Laravel doesn't use database prefixes when building queries. So if your databases are on the same server you could do this by building a raw query like this:

DB::insert("INSERT INTO mysql1.users SELECT * FROM mysql2.users");

Another option is pulling the data from one DB and inserting it into the other. Assuming you have a fairly large table, you should use chunking to ensure you don't load the entire table into memory.

If you are moving from the "mysql2" connection to your default connection it might look like this:

$users1 = DB::table('users');
$users2 = DB::connection('mysql2')->table('users');
$page = 0;
$size = 50;
while (true) {
    // get some records
    $users = $users2->skip($page * $size)->take($size)->get();
    // end the loop when there are no more results
    if ($users->count() === 0) {
        break;
    }
    // convert the results and each row to an array
    $user_array = $users->map(fn ($u) => (array)$u)->toArray();
    // save them into the new database
    $users1->insert($user_array);
    // increment the page for the next iteration
    $page  ;
}

I'd recommend using proper tools such as mysqldump to do this though.