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.
Currently only a Queries for the following Data
- All Products, Categories and Related Data
- All Customers
- All Orders/Invoices etc.
1. All Products and Related Data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
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'; DELETE FROM catalog_category_entity WHERE level > 1; |
2. All Customer Data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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`;<br /> SET FOREIGN_KEY_CHECKS=1; |
3. All Orders/Invoices etc.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
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`; */; ALTER TABLE `sales_order` AUTO_INCREMENT=1; SET FOREIGN_KEY_CHECKS = 1; |
4. All Company & Negotiable Quote Data (B2B EE)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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; |
5. All Catalog Permissions Data (B2B EE)
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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; |
6. All Customer Balance Data (B2B EE)
|
1 2 3 4 5 6 7 8 9 10 11 |
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; |
7. All Logging & Operation & Report Data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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; |
8. All Requisition List Data (B2B EE)
|
1 2 3 4 5 6 7 |
SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE `requisition_list`; TRUNCATE TABLE `requisition_list_item`; SET FOREIGN_KEY_CHECKS = 1; |
9. All Shared Catalog Data (B2B EE)
|
1 2 3 4 5 6 7 8 |
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; |
10. Third Party Data
|
1 2 3 4 5 6 7 8 9 10 |
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; |
