Sometimes you wish to remove Sample Data or even Test Data before going live with the webshop. In this article there are several queries which you can use to remove specific elements.
1. All Products and Related Data
SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE `catalog_category_product`; TRUNCATE TABLE `catalog_category_product_index`; TRUNCATE TABLE `catalog_category_product_index_tmp`; TRUNCATE TABLE `catalog_compare_item`; TRUNCATE TABLE `catalog_product_bundle_option`; TRUNCATE TABLE `catalog_product_bundle_option_value`; TRUNCATE TABLE `catalog_product_bundle_price_index`; TRUNCATE TABLE `catalog_product_bundle_selection`; TRUNCATE TABLE `catalog_product_bundle_selection_price`; TRUNCATE TABLE `catalog_product_bundle_stock_index`; TRUNCATE TABLE `catalog_product_entity`; TRUNCATE TABLE `catalog_product_entity_datetime`; TRUNCATE TABLE `catalog_product_entity_decimal`; TRUNCATE TABLE `catalog_product_entity_gallery`; TRUNCATE TABLE `catalog_product_entity_int`; TRUNCATE TABLE `catalog_product_entity_media_gallery`; TRUNCATE TABLE `catalog_product_entity_media_gallery_value`; TRUNCATE TABLE `catalog_product_entity_media_gallery_value_to_entity`; TRUNCATE TABLE `catalog_product_entity_media_gallery_value_video`; TRUNCATE TABLE `catalog_product_entity_text`; TRUNCATE TABLE `catalog_product_entity_tier_price`; TRUNCATE TABLE `catalog_product_entity_varchar`; TRUNCATE TABLE `catalog_product_index_eav`; TRUNCATE TABLE `catalog_product_index_eav_decimal`; TRUNCATE TABLE `catalog_product_index_eav_decimal_idx`; TRUNCATE TABLE `catalog_product_index_eav_decimal_tmp`; TRUNCATE TABLE `catalog_product_index_eav_idx`; TRUNCATE TABLE `catalog_product_index_eav_tmp`; TRUNCATE TABLE `catalog_product_index_price`; TRUNCATE TABLE `catalog_product_index_price_bundle_idx`; TRUNCATE TABLE `catalog_product_index_price_bundle_opt_idx`; TRUNCATE TABLE `catalog_product_index_price_bundle_opt_tmp`; TRUNCATE TABLE `catalog_product_index_price_bundle_sel_idx`; TRUNCATE TABLE `catalog_product_index_price_bundle_sel_tmp`; TRUNCATE TABLE `catalog_product_index_price_bundle_tmp`; TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_idx`; TRUNCATE TABLE `catalog_product_index_price_cfg_opt_agr_tmp`; TRUNCATE TABLE `catalog_product_index_price_cfg_opt_idx`; TRUNCATE TABLE `catalog_product_index_price_cfg_opt_tmp`; TRUNCATE TABLE `catalog_product_index_price_downlod_idx`; TRUNCATE TABLE `catalog_product_index_price_downlod_tmp`; TRUNCATE TABLE `catalog_product_index_price_final_idx`; TRUNCATE TABLE `catalog_product_index_price_final_tmp`; TRUNCATE TABLE `catalog_product_index_price_idx`; TRUNCATE TABLE `catalog_product_index_price_opt_agr_idx`; TRUNCATE TABLE `catalog_product_index_price_opt_agr_tmp`; TRUNCATE TABLE `catalog_product_index_price_opt_idx`; TRUNCATE TABLE `catalog_product_index_price_opt_tmp`; TRUNCATE TABLE `catalog_product_index_price_tmp`; TRUNCATE TABLE `catalog_product_index_tier_price`; TRUNCATE TABLE `catalog_product_index_website`; TRUNCATE TABLE `catalog_product_link`; TRUNCATE TABLE `catalog_product_link_attribute`; TRUNCATE TABLE `catalog_product_link_attribute_decimal`; TRUNCATE TABLE `catalog_product_link_attribute_int`; TRUNCATE TABLE `catalog_product_link_attribute_varchar`; TRUNCATE TABLE `catalog_product_link_type`; TRUNCATE TABLE `catalog_product_option`; TRUNCATE TABLE `catalog_product_option_price`; TRUNCATE TABLE `catalog_product_option_title`; TRUNCATE TABLE `catalog_product_option_type_price`; TRUNCATE TABLE `catalog_product_option_type_title`; TRUNCATE TABLE `catalog_product_option_type_value`; TRUNCATE TABLE `catalog_product_relation`; TRUNCATE TABLE `catalog_product_super_attribute`; TRUNCATE TABLE `catalog_product_super_attribute_label`; TRUNCATE TABLE `catalog_product_super_link`; TRUNCATE TABLE `catalog_product_website`; TRUNCATE TABLE `catalog_url_rewrite_product_category`; TRUNCATE TABLE `cataloginventory_stock`; TRUNCATE TABLE `cataloginventory_stock_item`; TRUNCATE TABLE `cataloginventory_stock_status`; TRUNCATE TABLE `cataloginventory_stock_status_idx`; TRUNCATE TABLE `cataloginventory_stock_status_tmp`; TRUNCATE TABLE `downloadable_link`; TRUNCATE TABLE `downloadable_link_price`; TRUNCATE TABLE `downloadable_link_purchased`; TRUNCATE TABLE `downloadable_link_purchased_item`; TRUNCATE TABLE `downloadable_link_title`; TRUNCATE TABLE `downloadable_sample`; TRUNCATE TABLE `downloadable_sample_title`; TRUNCATE TABLE `product_alert_price`; TRUNCATE TABLE `product_alert_stock`; TRUNCATE TABLE `report_compared_product_index`; TRUNCATE TABLE `report_viewed_product_aggregated_daily`; TRUNCATE TABLE `report_viewed_product_aggregated_monthly`; TRUNCATE TABLE `report_viewed_product_aggregated_yearly`; TRUNCATE TABLE `report_viewed_product_index`; TRUNCATE TABLE `sequence_product`; INSERT INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell'); INSERT INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal'); INSERT INTO `cataloginventory_stock`(`stock_id`,`website_id`,`stock_name`) VALUES (1,0,'Default'); SET FOREIGN_KEY_CHECKS = 1; DELETE FROM url_rewrite where entity_type = 'product';
2. All Categories
DELETE FROM url_rewrite where entity_type = 'category'; DELETE FROM catalog_category_entity WHERE level > 1;
3. All Customer Data
SET FOREIGN_KEY_CHECKS=0; TRUNCATE TABLE `customer_entity`; TRUNCATE TABLE `customer_entity_datetime`; TRUNCATE TABLE `customer_entity_decimal`; TRUNCATE TABLE `customer_entity_int`; TRUNCATE TABLE `customer_entity_text`; TRUNCATE TABLE `customer_entity_varchar`; TRUNCATE TABLE `customer_address_entity`; TRUNCATE TABLE `customer_address_entity_datetime`; TRUNCATE TABLE `customer_address_entity_decimal`; TRUNCATE TABLE `customer_address_entity_int`; TRUNCATE TABLE `customer_address_entity_text`; TRUNCATE TABLE `customer_address_entity_varchar`; TRUNCATE TABLE `customer_grid_flat`; TRUNCATE TABLE `customer_log`; TRUNCATE TABLE `customer_visitor`; TRUNCATE TABLE `persistent_session`; TRUNCATE TABLE `salesrule_customer`; TRUNCATE TABLE `magento_customerbalance`; TRUNCATE TABLE `magento_customerbalance_history`; TRUNCATE TABLE `magento_customercustomattributes_sales_flat_order`; TRUNCATE TABLE `magento_customercustomattributes_sales_flat_order_address`; TRUNCATE TABLE `magento_customercustomattributes_sales_flat_quote`; TRUNCATE TABLE `magento_customercustomattributes_sales_flat_quote_address`; TRUNCATE TABLE `magento_customersegment_customer`; TRUNCATE TABLE `magento_customersegment_event`; TRUNCATE TABLE `magento_customersegment_event`; TRUNCATE TABLE `magento_customersegment_website`; TRUNCATE TABLE `magento_targetrule_customersegment`; TRUNCATE TABLE `salesrule_customer`; TRUNCATE TABLE `email_catalog`; TRUNCATE TABLE `email_abandoned_cart`; TRUNCATE TABLE `email_automation`; TRUNCATE TABLE `email_campaign`; TRUNCATE TABLE `email_catalog`; TRUNCATE TABLE `email_contact`; TRUNCATE TABLE `email_contact_consent`; TRUNCATE TABLE `email_failed_auth`; TRUNCATE TABLE `email_importer`; TRUNCATE TABLE `email_order`; TRUNCATE TABLE `email_review`; TRUNCATE TABLE `email_rules`; TRUNCATE TABLE `email_wishlist`; TRUNCATE TABLE `wishlist`; TRUNCATE TABLE `wishlist_item`; TRUNCATE TABLE `wishlist_item_option`;
SET FOREIGN_KEY_CHECKS=1;
4. All Orders/Invoices etc.
SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE `gift_message`; TRUNCATE TABLE `quote`; TRUNCATE TABLE `quote_address`; TRUNCATE TABLE `quote_address_item`; TRUNCATE TABLE `quote_id_mask`; TRUNCATE TABLE `quote_item`; TRUNCATE TABLE `quote_item_option`; TRUNCATE TABLE `quote_payment`; TRUNCATE TABLE `quote_shipping_rate`; TRUNCATE TABLE `reporting_orders`; TRUNCATE TABLE `sales_bestsellers_aggregated_daily`; TRUNCATE TABLE `sales_bestsellers_aggregated_monthly`; TRUNCATE TABLE `sales_bestsellers_aggregated_yearly`; TRUNCATE TABLE `sales_creditmemo`; TRUNCATE TABLE `sales_creditmemo_comment`; TRUNCATE TABLE `sales_creditmemo_grid`; TRUNCATE TABLE `sales_creditmemo_item`; TRUNCATE TABLE `sales_invoice`; TRUNCATE TABLE `sales_invoiced_aggregated`; TRUNCATE TABLE `sales_invoiced_aggregated_order`; TRUNCATE TABLE `sales_invoice_comment`; TRUNCATE TABLE `sales_invoice_grid`; TRUNCATE TABLE `sales_invoice_item`; TRUNCATE TABLE `sales_order`; TRUNCATE TABLE `sales_order_address`; TRUNCATE TABLE `sales_order_aggregated_created`; TRUNCATE TABLE `sales_order_aggregated_updated`; TRUNCATE TABLE `sales_order_grid`; TRUNCATE TABLE `sales_order_item`; TRUNCATE TABLE `sales_order_payment`; TRUNCATE TABLE `sales_order_status_history`; TRUNCATE TABLE `sales_order_tax`; TRUNCATE TABLE `sales_order_tax_item`; TRUNCATE TABLE `sales_payment_transaction`; TRUNCATE TABLE `sales_refunded_aggregated`; TRUNCATE TABLE `sales_refunded_aggregated_order`; TRUNCATE TABLE `sales_shipment`; TRUNCATE TABLE `sales_shipment_comment`; TRUNCATE TABLE `sales_shipment_grid`; TRUNCATE TABLE `sales_shipment_item`; TRUNCATE TABLE `sales_shipment_track`; TRUNCATE TABLE `sales_shipping_aggregated`; TRUNCATE TABLE `sales_shipping_aggregated_order`; TRUNCATE TABLE `tax_order_aggregated_created`; TRUNCATE TABLE `tax_order_aggregated_updated`; TRUNCATE TABLE `vault_payment_token_order_payment_link`; TRUNCATE TABLE `sequence_order_0`; TRUNCATE TABLE `sequence_invoice_0`; TRUNCATE TABLE `sequence_shipment_0`; TRUNCATE TABLE `sequence_creditmemo_0`; TRUNCATE TABLE `sequence_order_1`; TRUNCATE TABLE `sequence_invoice_1`; TRUNCATE TABLE `sequence_shipment_1`; TRUNCATE TABLE `sequence_creditmemo_1`; /*Add According to your store ids */; /*example: TRUNCATE TABLE `sequence_order_2`; */; /** Company module **/ /** TRUNCATE TABLE `company_order_entity`; **/ ALTER TABLE `sales_order` AUTO_INCREMENT=1; SET FOREIGN_KEY_CHECKS = 1;
5. All Company & Negotiable Quote Data (B2B EE)
SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE `company`; TRUNCATE TABLE `company_advanced_customer_entity`; TRUNCATE TABLE `company_credit`; TRUNCATE TABLE `company_credit_history`; TRUNCATE TABLE `company_order_entity`; TRUNCATE TABLE `company_payment`; TRUNCATE TABLE `company_permissions`; TRUNCATE TABLE `company_roles`; TRUNCATE TABLE `company_structure`; TRUNCATE TABLE `company_team`; TRUNCATE TABLE `company_user_roles`; TRUNCATE TABLE `negotiable_quote`; TRUNCATE TABLE `negotiable_quote_comment`; TRUNCATE TABLE `negotiable_quote_comment_attachment`; TRUNCATE TABLE `negotiable_quote_company_config`; TRUNCATE TABLE `negotiable_quote_grid`; TRUNCATE TABLE `negotiable_quote_history`; TRUNCATE TABLE `negotiable_quote_item`; TRUNCATE TABLE `negotiable_quote_purged_content`; SET FOREIGN_KEY_CHECKS = 1;
6. All Catalog Permissions Data (B2B EE)
SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE `magento_catalogpermissions`; TRUNCATE TABLE `magento_catalogpermissions_index`; TRUNCATE TABLE `magento_catalogpermissions_index_product`; TRUNCATE TABLE `magento_catalogpermissions_index_product_replica`; TRUNCATE TABLE `magento_catalogpermissions_index_product_tmp`; TRUNCATE TABLE `magento_catalogpermissions_index_replica`; TRUNCATE TABLE `magento_catalogpermissions_index_tmp`; SET FOREIGN_KEY_CHECKS = 1;
7. All Customer Balance Data (B2B EE)
SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE `magento_customerbalance`; TRUNCATE TABLE `magento_customerbalance_history`; TRUNCATE TABLE `magento_customercustomattributes_sales_flat_order`; TRUNCATE TABLE `magento_customercustomattributes_sales_flat_order_address`; TRUNCATE TABLE `magento_customercustomattributes_sales_flat_quote`; TRUNCATE TABLE `magento_customercustomattributes_sales_flat_quote_address`; SET FOREIGN_KEY_CHECKS = 1;
8. All Logging & Operation & Report Data
SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE `magento_logging_event`; TRUNCATE TABLE `magento_logging_event_changes`; TRUNCATE TABLE `magento_operation`; TRUNCATE TABLE `report_event`; TRUNCATE TABLE `report_viewed_product_aggregated_daily`; TRUNCATE TABLE `report_viewed_product_aggregated_monthly`; TRUNCATE TABLE `report_viewed_product_aggregated_yearly`; TRUNCATE TABLE `report_viewed_product_index`; SET FOREIGN_KEY_CHECKS = 1;
9. All Requisition List Data (B2B EE)
SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE `requisition_list`; TRUNCATE TABLE `requisition_list_item`; SET FOREIGN_KEY_CHECKS = 1;
10. All Shared Catalog Data (B2B EE)
SET FOREIGN_KEY_CHECKS = 0; DELETE FROM `shared_catalog` WHERE entity_id != 1; TRUNCATE TABLE `shared_catalog_product_item`; TRUNCATE TABLE `sharedcatalog_category_permissions`; SET FOREIGN_KEY_CHECKS = 1;
11. Third Party Data
SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE `webforms_results`; TRUNCATE TABLE `webforms_results_values`; TRUNCATE TABLE `watchlog`; TRUNCATE TABLE `magefan_login_as_customer`; SET FOREIGN_KEY_CHECKS = 1;
