How To Bulk Insert in A Table Magento 2

How To Bulk Insert in A Table Magento 2 : Sometime we need to insert multiple records in table using a single step. because insert record one by one it takes so much time. so for the better performance, you can insert multiple records in a table if you want. and it will take less time. so i am going to explain how you can insert bulk records in a database table.

1 => First make a class where you will define the connection with table for bulk insert.
so here i have created a class in model directory on this location (Webkul\BulkInsertTest\Model\Storage).

namespace Webkul\BulkInsertTest\Model\Storage;

use Magento\Framework\App\ResourceConnection;

class DbStorage
{
    /**
     * DB Storage table name
     */
    const TABLE_NAME = 'wk_custom_table';

    /**
     * Code of "Integrity constraint violation: 1062 Duplicate entry" error
     */
    const ERROR_CODE_DUPLICATE_ENTRY = 23000;

    /**
     * @var \Magento\Framework\DB\Adapter\AdapterInterface
     */
    protected $connection;

    /**
     * @var Resource
     */
    protected $resource;

    /**
     * @param \Magento\Framework\App\ResourceConnection $resource
     */
    public function __construct(
        ResourceConnection $resource
    ) {
        $this->connection = $resource->getConnection();
        $this->resource = $resource;
    }

    /**
     * Insert multiple
     *
     * @param array $data
     * @return void
     * @throws \Magento\Framework\Exception\AlreadyExistsException
     * @throws \Exception
     */
    public function insertMultiple($data, $tableName = self::TABLE_NAME)
    {
        try {
            $tableName = $this->resource->getTableName(self::TABLE_NAME);
            return $this->connection->insertMultiple($tableName, $data);
        } catch (\Exception $e) {
            if ($e->getCode() === self::ERROR_CODE_DUPLICATE_ENTRY
                && preg_match('#SQLSTATE\[23000\]: [^:]+: 1062[^\d]#', $e->getMessage())
            ) {
                throw new \Magento\Framework\Exception\AlreadyExistsException(
                    __('URL key for specified store already exists.')
                );
            }
            throw $e;
        }
    }
}

Here TABLE_NAME constant contain table name.

2 => Now you have to just create an array of data and call the method.

foreach($records as $record) {
    $bulkInsert[] = [
        'first_name' => $record['first_name'],
        'last_name' => $record['last_name']
    ];
}

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$dbStorage = $objectManager->get('Webkul\BulkInsertTest\Model\Storage');
$dbStorage->insertMultiple($bulkInsert, 'wk_custom_table',);

Hope so it will help.

Narendra 6 Badges

• 6 Jan, 2023
  Updated by - Ajay Singh
• 9 Dec, 2017
  Created by - Narendra