# How to I insert into one database/table data from a select from another database/table?

**URL:** <https://discourse.laminas.dev/t/how-to-i-insert-into-one-database-table-data-from-a-select-from-another-database-table/3637>\
**Category:** Components & MVC\
**Tags:** laminas-db\
**Created:** [March 14, 2024, 6:21pm UTC](https://discourse.laminas.dev/t/how-to-i-insert-into-one-database-table-data-from-a-select-from-another-database-table/3637 "2024-03-14T18:21:05Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![COMCDUARTE](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/comcduarte/32/1005_2.png) [@COMCDUARTE](https://discourse.laminas.dev/u/COMCDUARTE)\
**Post date:** [March 14, 2024, 6:21pm UTC](https://discourse.laminas.dev/t/how-to-i-insert-into-one-database-table-data-from-a-select-from-another-database-table/3637/1 "2024-03-14T18:21:05Z")

</div>

Project: I have a separate production and development database. Periodically I’d like to update most of the data in development with recent entries in production.

Currently I’ve been relying on MySQL procedures which simply drop the table and create new tables from selecting all records in production. (i.e. CREATE TABLE dev.table AS (SELECT \* FROM prod.table); This is becoming inefficient.

Goal: From within my application, I’d like to be able to initiate an update. Every record has a date created and date modified field. Simply, select the latest record based on date created, DESC LIMIT 1, and retrieve the date. Then insert into dev (select from prod where date\_created \> $var).

This isn’t that bad, until you put the two databases into the equation. Here’s something along the lines of what I’m looking for.

$sql = new Sql($production\_adapter)  
…  
$select = new Select();  
$select-\>from($table)-\>where($where-\>greaterThan(‘DATE\_CREATED’, $target\_date));  
…  
$insert = new Insert();  
$insert-\>into($table)-\>values($select);  
…  
$statement = $sql-\>prepareStatementForSqlObject($insert);  
$statement-\>execute();

I can get it to select and insert from the same database, but not to an alternate database. Any suggestions?

---

<div class="post-metadata">

**Author:** ![Tyrsson](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/tyrsson/32/1664_2.png) [@Tyrsson](https://discourse.laminas.dev/u/Tyrsson)\
**Post date:** [March 14, 2024, 10:35pm UTC](https://discourse.laminas.dev/t/how-to-i-insert-into-one-database-table-data-from-a-select-from-another-database-table/3637/2 "2024-03-14T22:35:14Z")

</div>

Laminas db supports using named adapters. Maybe this could help?

> **[Setting Up A Database Adapter - tutorials - Laminas Docs](https://docs.laminas.dev/tutorials/db-adapter/#configuring-named-adapters)**
>
> Learn how to create laminas-mvc applications, get in-depth guides into components, and discover how to migrate your applications to version 3!

---

<div class="post-metadata">

**Author:** ![COMCDUARTE](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/comcduarte/32/1005_2.png) [@COMCDUARTE](https://discourse.laminas.dev/u/COMCDUARTE)\
**Post date:** [March 15, 2024, 2:08pm UTC](https://discourse.laminas.dev/t/how-to-i-insert-into-one-database-table-data-from-a-select-from-another-database-table/3637/3 "2024-03-15T14:08:50Z")

</div>

I did setup two named adapters.

```auto
'services' => [
    'dev-model-adapter-config' => [
        'driver' => 'PDO',
        'dsn' => 'mysql:host=host;dbname=database_dev',
        'username' => 'user',
        'password' => 'pass',
    ],
    'prod-model-adapter-config' => [
        'driver' => 'PDO',
        'dsn' => 'mysql:host=host;dbname=database_prod',
        'username' => 'user',
        'password' => 'pass',
    ],
],

```

Normally though, when preparing SQL statement, one adapter is passed. I’ve used multiple adapters before to pull data from two different databases, but looking for an easy efficient way to reference to databases (two named adapters) from one SQL statement.

```auto
$sql = new Sql($this->production_adapter);
...
$statement = $sql->prepareStatementForSqlObject($select);

```

---

<div class="post-metadata">

**Author:** ![Tyrsson](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/tyrsson/32/1664_2.png) [@Tyrsson](https://discourse.laminas.dev/u/Tyrsson)\
**Post date:** [March 17, 2024, 1:14am UTC](https://discourse.laminas.dev/t/how-to-i-insert-into-one-database-table-data-from-a-select-from-another-database-table/3637/4 "2024-03-17T01:14:39Z")

</div>

You would need a secondary SQL instance as far as I am aware tied to the secondary adapter.

---

<div class="post-metadata">

**Author:** ![ALTAMASH80](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/altamash80/32/895_2.png) [@ALTAMASH80](https://discourse.laminas.dev/u/ALTAMASH80)\
**Post date:** [March 17, 2024, 8:38am UTC](https://discourse.laminas.dev/t/how-to-i-insert-into-one-database-table-data-from-a-select-from-another-database-table/3637/5 "2024-03-17T08:38:37Z")

</div>

Hi @COMCDUARTE,

I think you need to change your configuration key. You’ve to follow the framework’s guidelines and use the appropriate keys. You’ve used an imaginary key and thought that it would work. So it would be best to change the following key as suggested in the documentation.

```auto
return [
  'db'/*Changed to db instead of services as suggested.*/ => [
     'dev-model-adapter-config' => [...],
     'prod-model-adapter-config' => [...],
  ],
...
];

```

Another suggestion would be to create a stored procedure. It would be much faster and memory efficient. Thanks!

---

<div class="post-metadata">

**Author:** ![Tyrsson](https://yyz2.discourse-cdn.com/flex032/user_avatar/discourse.laminas.dev/tyrsson/32/1664_2.png) [@Tyrsson](https://discourse.laminas.dev/u/Tyrsson)\
**Post date:** [March 17, 2024, 4:15pm UTC](https://discourse.laminas.dev/t/how-to-i-insert-into-one-database-table-data-from-a-select-from-another-database-table/3637/6 "2024-03-17T16:15:27Z")

</div>

I took it that the OP has somehow configured the application to correctly use the config shown since they can get it to read from two different DB’s currently, however for the sake of correctness see below.

According to the docs I linked to it should be:

```auto
'db' => [
     'adapters' => [
         // named adapter config here, this is what the AbstractFactory looks for
    ],
]

```

To see why please see:

> <https://github.com/laminas/laminas-db/blob/5d8c89d767d4dac7b710c8c6d967f3780f7a5f6a/src/Adapter/AdapterAbstractServiceFactory.php#L81>