How to create simple product using direct SQL queries?

Question

I know direct SQL query is not good practice, but I need to insert product using direct SQL query.
Also they do not want to use REST API for that.
Anyone has SQL queries to create a simple product with all the fields.

Answer 1

Magento using EAV Structure for store product data. So, using single query it quit impossible or direct query, save product data, not possible.

https://fishpig.co.uk/magento/tutorials/eav-database-structure/

There are a lot of tables and lots of related tables has used to save data for the product. So my suggestions to avoid the idea of using query save a product full data at the database, if you are new at Magento.

If you have talked about a single field/attribute save of this might be easy to answer. But you must have an idea of EAV data pattern.

Answer 2

This query can help you out

1. insert new product

INSERT INTO catalog_product_entity (entity_id, attribute_set_id, type_id, sku, has_options, required_options, created_at, updated_at) VALUES (NULL, '4', 'simple', 'my-sku-test', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); [get entity id of new product]

2. Set status of new product using entity_id

INSERT INTO catalog_product_entity_int (value_id, attribute_id, store_id, entity_id, value) VALUES (NULL, '97', '1', '17', '1');

3. Set visibility of product using entity_id

INSERT INTO catalog_product_entity_int (value_id, attribute_id, store_id, entity_id, value) VALUES (NULL, '99', '0', '17', '4')

4. Set tax_class_id of product

INSERT INTO catalog_product_entity_int (value_id, attribute_id, store_id, entity_id, value) VALUES (NULL, '134', '0', '17', '0')

5. Set filter_price_range of product

INSERT INTO catalog_product_entity_decimal (value_id, attribute_id, store_id, entity_id, value) VALUES (NULL, '77', '0', '17', '100');

6. Set weight of product

INSERT INTO catalog_product_entity_decimal (value_id, attribute_id, store_id, entity_id, value) VALUES (NULL, '82', '0', '17', '10')

7. Set quantity of product

INSERT INTO cataloginventory_stock_item (item_id, product_id, stock_id, qty, min_qty, use_config_min_qty, is_qty_decimal, backorders, use_config_backorders, min_sale_qty, use_config_min_sale_qty, max_sale_qty, use_config_max_sale_qty, is_in_stock, low_stock_date, notify_stock_qty, use_config_notify_stock_qty, manage_stock, use_config_manage_stock, stock_status_changed_auto, use_config_qty_increments, qty_increments, use_config_enable_qty_inc, enable_qty_increments, is_decimal_divided, website_id) VALUES (NULL, '17', '1', '1000', '0.0000', '1', '0', '0', '1', '1.0000', '1', '0.0000', '1', '0', NULL, NULL, '1', '0', '1', '0', '1', '0.0000', '1', '0', '0', '0');

8. Set name of product

INSERT INTO catalog_product_entity_varchar (value_id, attribute_id, store_id, entity_id, value) VALUES (NULL, '73', '0', '17', 'my-sku-test');

9. Set url of product

INSERT INTO catalog_product_entity_varchar (value_id, attribute_id, store_id, entity_id, value) VALUES (NULL, '121', '0', '17', 'my-sku-test');