CODEWITHSUNDEEP
phpzend-framework2bulkinsertinsert-into
Saswata Banerjee
Saswata Banerjee

Reputation: 23

ZF2 Bulk insert using INSERT INTO .... SELECT from a table to same table

I am trying to do a bulk copy of some records from a table into the same table with one field changed. But I am not able to find the correct method in ZF2 for it.

My existing table looks like this : CoID -- CatCode -- CategoryName

PP -- auto -- Auto

PP -- Mt -- Metro

PP -- Taxi -- Taxi

I want to run this SQL Query :

insert into expcatmaster select 'RY', CatCode, CategoryName from expcatmaster where CoID = 'PP'

I am not able to find a way to put the constant 'RY' into the code (using ZF2). The code in my CopyCategoryTable.php is as below :

public function insCategory($FromCoID, $ToCoID) {
    $SelCol = array(
        'CoID',
        'CatCode',
        'CategoryName',
    );

    $adapter = $this->tableGateway->getAdapter();
    $sql = new Sql($adapter);
    $SelQry =$sql->select();
    $SelQry->columns($SelCol);
    $SelQry->from('ExpcatMaster');
    $SelQry->where(array('CoID' => $FromCoID));
    echo $SelQry->getSqlString();

    $InsQry = $sql->insert();
    $InsQry->into('ExpcatMaster');
    $InsQry->select($SelQry);
    $InsData = $sql->prepareStatementForSqlObject($InsQry);
    echo $InsQry->getSqlString();

    $this->tableGateway = $InsData->execute();

}

How do i replace the actual value of CoID from the select table with $ToCoID i have passed to the function ? Tried google and searched in this forum but cant seem to work it out.

Upvotes: 2

Views: 1392

Answers (1)

tasmaniski
tasmaniski

Reputation: 4898

There is no method for bulk insert in ZF (Zend\Db), so you have to write a method for that.

Zend\Db\Sql\Sql multiple batch inserts

The bulk insert is supported in MySQL but not in all databases... Check discussion on link.

You can execute raw SQL query for this case:

$parameters = ['name 1', 'desc 1', 'name 2', 'desc 2'];
$sql        = 'INSERT INTO table (name, description) VALUES (?, ?), (?, ?);';
$resultSet  = $this->adapter->query($sql, $parameters);

return $resultSet->getAffectedRows();

Upvotes: 2

Related Questions