Magento 2: Insert Multiple Rows into Database Table

Facebook Tweet LinkedIn Pin Print EmailShares

This article shows how you can insert multiple rows or records into your database table.

Generally, we add a single row of data into the database table. However, we have situations where we need to insert multiple rows of data into the table at once.

We can do so by running the insert query multiple times but that will be memory resource intensive.

Here’s an example of how you can insert multiple rows of data by running the insert/save query multiple times:

Please note that after saving the rows, you need to unsetData, every time.


// Suppose, I have the following data
$myData = [
['id' => 1, 'name' => 'John', 'age' => 22],
['id' => 2, 'name' => 'Mary', 'age' => 23],
['id' => 3, 'name' => 'Sam', 'age' => 24]
];

// defining my model
$myModel = $this->myModel;

// Inserting data using for loop
foreach ($myData as $data) {
    $myModel->addData($data);
    $myModel->save();
    $myModel->unsetData(); // this line is necessary to save multiple records
}

More efficient way to insert multiple records

The above one is not an efficient way of adding multiple records.

We can use insertMultiple function of ResourceConnection class to add multiple records at once with a single query.


namespace YourNamespace\YourModule\Controller;

use Magento\Framework\App\Action\Context;
use Magento\Framework\Message\ManagerInterface;
use Magento\Framework\App\ResourceConnection;

class YourController extends \Magento\Framework\App\Action\Action
{
    /**
     * @var \Magento\Framework\Message\ManagerInterface
     */
    protected $messageManager;

    /**
     * @var \Magento\Framework\App\ResourceConnection $resource
     */
    protected $resource;

    protected $connection;

    public function __construct(
        Context $context,
        ManagerInterface $messageManager,
        ...
        ...
        ResourceConnection $resource
    )
    {
        $this->messageManager       = $messageManager;
        ...
        ...
        $this->resource             = $resource;
        $this->connection           = $resource->getConnection();

        parent::__construct($context);
    }

    public function insertMultiple($table, $data)
    {
        try {
            $tableName = $this->resource->getTableName($table);
            return $this->connection->insertMultiple($tableName, $data);
        } catch (\Exception $e) {
            $this->messageManager->addException($e, __('Cannot insert data.'));
        }
    }

    public function yourCustomFunction()
    {
        try {
            $yourData = [
                ['id' => 1, 'name' => 'John', 'age' => 22],
                ['id' => 2, 'name' => 'Mary', 'age' => 23],
                ['id' => 3, 'name' => 'Sam', 'age' => 24]
            ];
            
            $tableName = 'your_table_name';
            $this->insertMultiple($tableName, $yourData);
            
            $this->messageManager->addSuccess( __('Successfully inserted data.') );
        } catch (Exception $e) {
            $this->messageManager->addException($e, __('Cannot save data.'));
        }
    }
}

Hope this helps. Thanks.

More efficient way to insert multiple records

Related posts: