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)

Insert Multiple Records into Magento 2 Database Tables

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!

Insert Multiple Records into Magento 2 Database Tables
Tagged on:     

One thought on “Insert Multiple Records into Magento 2 Database Tables

Leave a Reply

Your email address will not be published. Required fields are marked *