How to use insertFromSelect query in database Magento 2 with best practice?

You can use INSERT IGNORE INTO mysql query with the help of insertFromSelect() method in Magento 2 with use of magento best standard in your module.

Lets we have a query that use the insertFromSelect method to run a query.

Raw SQL Query to add an entry to the small_image attribute from the image attribute value.

INSERT IGNORE INTO `catalog_product_entity_varchar` (`attribute_id`, `store_id`, `value`, `row_id`) SELECT 88, `store_id`, `value`, `row_id` FROM `catalog_product_entity_varchar` WHERE (value != 'no_selection') AND (attribute_id = 87);

You can create SQL queries with Magento best practices,

<?php
declare(strict_types=1);

namespace Rbj\InserFromSelect\Index;

use Magento\Framework\App\ResourceConnection;
use Magento\Framework\DB\Adapter\AdapterInterface;

class InsertFromSelect
{
    private const CATALOG_PRODUCT_ENTITY_VARCHAR = 'catalog_product_entity_varchar';

    public function __construct(
        private readonly ResourceConnection $resourceConnection
    ) {
    }

    public function execute()
    {
        $connection = $this->resourceConnection->getConnection();
        $catalogVarcharTable = $connection->getTableName(self::CATALOG_PRODUCT_ENTITY_VARCHAR);
        $smallImageAttributeId = 88;
        $imageAttributeId = 87;

        /** select query */
        $select = $connection->select()
            ->from(
                [$catalogVarcharTable],
                [new \Zend_Db_Expr($imageAttributeId), 'store_id', 'value', 'row_id']
            )
            ->where("value != 'no_selection'")
            ->where('attribute_id = ?', $smallImageAttributeId);
        $insertFromSelectQuery = $connection->insertFromSelect(
            $select,
            $catalogVarcharTable,
            ['attribute_id', 'store_id', 'value', 'row_id'],
            AdapterInterface::INSERT_IGNORE
        );
   
        /** Run Query */
        $connection->query($insertFromSelectQuery);
    }
}

In the above code, We have first created an inset ignore into query with the help of the method insertFromSelect(). In the method, insertFromSelect() a fourth parameter is used to add specific values.

You can use any of the given parameters as the fourth argument.

INSERT_ON_DUPLICATE = 1;
INSERT_IGNORE = 2;
REPLACE = 4;