Each order reduces discount by 1%
Rating 5 stars - 1 votes

What Is Custom SQL? How to Use Magento Custom SQLs for Data Management?

On any fast-paced Magento store, the task of keeping it under control and ever updated becomes of top priority. Oftentimes you have some specific requirements to data management, what respectively calls for advanced techniques to be used, for example direct SQL queries in Magento.

Store Manager for Magento adapts to each and every user, so you can run the queries directly from this application and make updates more flexible.

Magento Custom SQL Notion

Custom SQL can be defined as adjusted according to users’ needs SQL statement, applying which will return desired data input, update, removing, etc. Creating Magento custom SQL you actually outline certain conditions, adapted to your specific case.

For example, your have multiple products from various manufacturers in the import file and products of manufacturer B, for example, should be imported with “Out of stock” status. Creating special SQL command and applying it in the process of Magento import, you can easily carry out this task.

SQL will surely be effective when you have products, provided by certain supplier and are going to import them, but you have been selling some merchandise from this supplier already. Now you want to set the products you have been selling as "Out of stock", so that only imported products from this supplier will be in stock.

Custom SQLs in Store Manager

Magento custom SQLs are created, managed and stored in Tools -> Custom SQL section of Magento Store Manager.

Magento custom sql
Magento custom sql

List of all existing SQLs is provided to the left. When you select the one, its code will display in the main window and you can implement some edits, if there is a necessity.

Custom SQL Associations

You create direct SQL query in Magento on purpose to further apply it to category, product update or run after Magento import. Correspondingly, you have to link them to products, categories or product import.

Magento custom sql
Magento custom sql

If some SQL is assigned to merchandise, there will appear “Custom SQL” button on product toolbar. When you press it, SQL (SQLs) linked to products will display in dropdown. Select the query to implement changes for selected products.

Magento custom sql
Magento custom sql

If you have configured Magento custom SQL and assigned it to categories, “Custom SQL” option will appear on category toolbar, as it is shown at the screenshot.

Magento custom sql
Magento custom sql

All queries, assigned to product import, are accessible at Import Options step of Store Manager import wizard.

Magento custom sql
Magento custom sql

All queries, assigned to product import, are accessible at Import Options step of Store Manager import wizard.

Store Manager comes with ready-made SQL commands that relate to Magento data import and allow to:

  • remove products, if they have not been listed in the import file. It means just uploaded from the file products will remain in Magento, items listed at your store before import, will be erased;
  • disable products, not available in the import file. Suppose you import products from the file and temporarily want to set existing at Magento goods as “Disabled”. Having enabled this option in the import wizard, you will get product status changed;
  • mark products not listed in the file as “Out of Stock”. Analogically, this Magento custom SQL allows to process merchandise, existing at Magento products. After import they will acquire “Out of Stock” status.

Creating Custom SQLs

If you are technically competent and have SQL skills, you can easily adjust data via direct SQL queries in Magento.

Please note, it is recommended to back up Magento database before applying Magento custom SQL.
  1. Start creating SQL command from pressing New button on the toolbar, like it is shown at the screenshot.

  2. Name the query to be added and confirm the title pressing OK.

  3. Magento custom sql
    Magento custom sql

  4. It will immediately appear in the list of custom SQLs. Highlight it and move to main window to configure commands it should stand for. Use “Insert Custom Parameters” and “Insert Macro” options accessible on product toolbar. SQL help will prompt you and help build workable SQL statement.

  5. Magento custom sql
    Magento custom sql

  6. Associate SQL command to product, category update or product import.

  7. Magento custom sql
    Magento custom sql

Magento custom SQL that has been just determined is available in Custom SQL dropdown at the product toolbar and one can run it for product data update.

Custom SQL provides more flexible way to manage Magento database. This functionality is highly convenient and enhances data handling. When creating custom SQLs store owners can indicate specific parameters and link commands to necessary entities (products, categories) or to product import.

Experience all the advantages of flexible data management tools of Store Manager for MagentoGet It Now

Related Articles:

Card image

How to Reindex Your Magento 2 Store

Find out how to set up Magento 2 reindex status and get step-by-step instructions on performing the reindex in manual mode or using Store Manager. Read More...

Card image

Magento 2 SEO Features

The ultimate guide on Magento 2 SEO optimization (XML map generation, meta tags optimization, speed up tools, product reviews etc...) Read More...

Card image

Automated Magento BackUps Running on Schedule

Schedule automatic Magento database backup on daily, weekly or monthly basis or set it up to be performed every few hours. Read More...

Card image

How to Perform Automated Product Import

Schedule Magento import to be accomplished on hourly, daily or weekly basis using the saved configuration. Read More...

Be the first to comment.