Insert Multiple Records into Magento 2 Database Tables

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!