In this tutorial, we’ll explore a streamlined approach to Insert Multiple Records into Magento 2 Database Tables. While the standard practice involves using Model or Factory to add individual records, there are scenarios where inserting multiple rows becomes a necessity.
Challenges with Traditional Approaches
Developers typically create a factory model class and utilize a foreach loop to insert multiple records. However, this method may lead to a slowdown in performance, especially when dealing with a large dataset.
A More Efficient Method to Insert Multiple Records into Magento 2 Database Tables
Our approach aims to enhance performance and expedite the process. Instead of relying on the conventional factory with a foreach loop, we’ll employ a more efficient method. Let’s examine the code below for more details.
Base Definition of the function:
Magento\Framework\DB\Adapter\AdapterInterface::insertMultiple($table, array $data);
public function insertMultiple($table, array $data);
<?php
/**
* @Module : Jrb Demomodule
* @Package : Jrb_Demomodule
* @Description : Testing custom script output
* @Developer : Jyotiranjan Biswal<biswal@jyotiranjan.in>
* @Copyright : https://www.jyotiranjan.in/
*/
namespace Jrb\DemoModule\Model;
use Magento\Framework\Model\AbstractModel;
use Magento\Framework\App\ResourceConnection;
class CustomScript extends AbstractModel
{
protected $logger;
/**
* Resource
*
* @var ResourceConnection
*/
protected $resourceConnection;
/**
* Constructor
*
* @param \Psr\Log\LoggerInterface $logger
*/
public function __construct(
\Psr\Log\LoggerInterface $logger,
ResourceConnection $resourceConnection,
)
{
$this->logger = $logger;
$this->resourceConnection = $resourceConnection;
}
/**
* Execute the custom script
*
* @return void
*/
public function execute()
{
$data = [
[
'name' => 'Ajay',
'salary' => '500',
'address' => 'demo address, street, pincode'
],
[
'name' => 'Vijaya',
'salary' => '1000',
'address' => 'demo address1, street1, pincode1'
],
];
$this->insertMultipleRecords($data);
}
/**
* @param array $data
*
* @return void
*/
public function insertMultipleRecords($data) {
try {
$connection = $this->resourceConnection->getConnection();
$tableName = $this->resourceConnection->getTableName('employee_details');
$connection->insertMultiple($tableName, $data);
} catch (\Exception $e) {
$this->logger->info('error occured while inserting multiple record'.$e->getMessage());
}
}
}
The key advantage of this method is its significant speed improvement over the traditional factory with a foreach loop. This ensures a quick insertion process and contributes to enhancing overall website speed performance.
You can also refer the some example from magento2 core files like Magento\Catalog\Model\ResourceModel\Category::_saveCategoryProducts($category)
You may also like this : How to Write SQL Query in Magento 2 With Standard Way
Conclusion
This tutorial provides a comprehensive understanding of efficiently performing bulk insertion into a Magento 2 database table. If you have any questions, suggestions, or if something was inadvertently overlooked. I’ll promptly address your queries.
Keep sharing and liking for more insightful tutorials!
Pingback:How to insert select update and delete data in magento2 - Jyotiranjan