How to perform an INSERT INTO SELECT query in ZF2

Question

What’s the best way to perform an INSERT INTO SELECT query in ZF2?

I need to develop a function in ZF2 that selects a subset of records from one table and inserts those records into another table. If I were programming in SQL, the statement would look like this:

INSERT INTO target (tgt_col1, tgt_col2) 
SELECT 'flag' as marker, src_col2 FROM source 
WHERE src_col1='mycriteria'

I’ve searched the Doctrine docs and cannot find an INSERT method. I’ve posted a similar question under the Doctrine tag and the lack of response leads me to believe that INSERT INTO SELECT is too complex for Doctrine to handle.

In ZF2 it appears that I “could” useZend\Db\Sql\Sql. However, for that solution, SELECT and INSERT are two separate functions and it looks like the INSERT function only handles a single record at a time. Therefore, a Zend\Db\Sql\Sql solution would require 1) a group of statements to select the data from the source table, 2) perhaps a function to convert that data object to an array, and 3) a foreach function to cycle through the data array and individually add each record to the target table via 4) a group of insert statements. This seems cumbersome compared to the single statement that’s possible in SQL.

Is there a better way?

Answer 1

If its TableGateway then it definitely works with the latest version of ZF2 i.e. 2.3.2

With reference with the Album module -

1. Created a duplicate table of album and named it album_old.
2. Emptied the album table.

So now album table is empty and album_old has the values.

To copy the records from album_old to album, did this -

use Zend\Db\Sql\Select;
....
use Zend\Db\Sql\Insert;

$select = new Select('album_old');
$select->columns(array('artist', 'title', 'cover', 'extra'));

$insert = new Insert();
$insert->into('album');
$insert->columns(array('artist', 'title', 'cover', 'extra'));

$insert->values($select);
//OR
$insert->select($select);

$this->tableGateway->insertWith($insert);

All the values from album_old were inserted into album table.

Ref: https://github.com/zendframework/zf2/blob/master/library/Zend/Db/Sql/Insert.php
Here you will find that the function values() can take array or instanceof Select
and there is a new select() function too.