How to execute INSERT INTO SELECT query in ZF2
What's the best way to execute 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 can't find the INSERT method. Ive posted a similar question in the Doctrine tag, and the lack of an answer makes me think that INSERT INTO SELECT is too complex for Doctrine to handle.
In ZF2 it seems like I "can" use Zend\Db\Sql\Sql
. However, for this solution, SELECT
both INSERT
are two separate functions, and it looks like the function INSERT
only processes one record at a time. So the solution Zend\Db\Sql\Sql
requires 1) a group of operators to select the data from the source table, 2) maybe a function to convert that data object to an array, and 3) a function foreach
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 only statement that is possible in SQL.
Is there a better way?
source to share
If its TableGateway
, then it definitely works with the latest version of ZF2, i.e. 2.3.2
With reference to the module Album
-
- Created a duplicate table
Album
and named italbum_old
. - Empty the table
Album
.
So now the Album
table is empty and album_old
matters.
To copy 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 values from album_old
have been inserted into the table Album
.
Link: https://github.com/zendframework/zf2/blob/master/library/Zend/Db/Sql/Insert.php
Here you will discover what the new function function values()
can accept array
or instanceof Select
is select()
.
source to share