Two database connection in one query in Laravel

Question

I need to use INSERT INTO...SELECT but using two different database in Laravel. Database A is a local database. Database B is a remote database.

I need something like this :

INSERT INTO local.table1
SELECT * 
FROM remote.table1
ON DUPLICATE KEY UPDATE col1=col1

Is there any way I could achieve this in laravel? Thanks!

u want to select rows from local and insert rows into remote database..am i right? — Jignesh Joisar, Feb 04 '19 at 07:15
no, I want to select rows from remote and insert into local database @JigneshJoisar — user7414328, Feb 04 '19 at 07:20

score 4 · Answer 1 · answered Feb 04 '19 at 07:22

4

You can have multiple database connections in Laravel. Follow this post.

Then to utilise it :

<?php 

$selectQuery = \DB::connection('remote')
    ->table('table1')
    ->select('column1','column2','column3');

\DB::connection('local')->insert('INSERT INTO table1 (column1, column2, column3) ' . $selectQuery->toSql(), $selectQuery->getBindings());

answered Feb 04 '19 at 07:22

Mihir Bhende

8,677
1
30
37

This doesn't work. It seems selectQuery uses 'local' instead of 'remote'. Is it possible I need something else to make this work? – user7414328 Feb 06 '19 at 01:50
If its different servers altogether then you need to create a table with [federated](https://dev.mysql.com/doc/refman/8.0/en/federated-storage-engine.html) engine. – Mihir Bhende Feb 06 '19 at 03:14

Two database connection in one query in Laravel

1 Answers1