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,


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()
                [new \Zend_Db_Expr($imageAttributeId), 'store_id', 'value', 'row_id']
            ->where("value != 'no_selection'")
            ->where('attribute_id = ?', $smallImageAttributeId);
        $insertFromSelectQuery = $connection->insertFromSelect(
            ['attribute_id', 'store_id', 'value', 'row_id'],
        /** Run Query */

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.