CODEWITHSUNDEEP
phplaravel-5.1laravel-5
Arvind
Arvind

Reputation: 857

Copy one table to another in laravel 5

How do i use the following insert into query in laravel 5?

INSERT INTO connection2.table (SELECT * from connection1.table);

I am looking for two different connections, Connection1.table record should goes to Connection2.table.

Upvotes: 8

Views: 12140

Answers (5)

jave.web
jave.web

Reputation: 15032

Since laravel 5.7+ insertUsing(array $columns, Closure|Builder|string $query) is available,
DOCS: https://laravel.com/api/5.8/Illuminate/Database/Query/Builder.html#method_insertUsing
therefore you can now make this whole query in Laravel chaining style like

DB::table('newTable')->insertUsing(
    ['column1', 'column2', 'column3',], // ..., columnN
    function ($query) {
        $query
            ->select(['column1', 'column2', 'column3',]) // ..., columnN
            ->from('oldTable');
            // optional: you could even add some conditions:
            // ->where('some_column', '=', 'somevalue')
            // ->whereNotNull('someColumn')
    }
);

DB here is set-up in app.php 'aliases' as
'DB' => Illuminate\Support\Facades\DB::class,

Upvotes: 3

Aishwarya Kumar
Aishwarya Kumar

Reputation: 61

$basket_data = DB::table('baskets')->get();
foreach($basket_data as $records)
{
    DB::table('basket_after_payments')->insert(get_object_vars($records));
}

Upvotes: 0

Jordan H
Jordan H

Reputation: 761

Elaborating on how to chunk results in case of large tables. You can of course chunk as you see fit, 5000 is just an example.

$chunk1 = DB("Connection1")->table("table")->orderBy("id")->chunk(5000, function($c1) {

    foreach($c1 as $record) {

        DB("Connection2")->table("table")->insert(get_object_vars($record))

    }
});

Upvotes: 1

Wistar
Wistar

Reputation: 3790

try

$c1 = DB("Connection1")->select("SELECT * from table")

foreach($c1 as $record){

    DB("Connection2")->table("table")->insert(get_object_vars($record))

}

Upvotes: 10

Arvind
Arvind

Reputation: 857

@Wistar, Thanks for reply, with your code $record comes with an object class which is not accepted by insert, it required the array type.

I have used it as follows:

   DB::setFetchMode(PDO::FETCH_ASSOC);
   $table_records = DB::connection('Connection1')->select("SELECT * from table");     
   DB::setFetchMode(PDO::FETCH_CLASS); 

   DB::connection('Connection2')->table("table")->insert($table_records);

Upvotes: 0

Related Questions