Thread: Help clearing out database

I would like to know if there is any easy way or a script to clear out the database of categories, products, sections, manufacturers...basically everything but the AppConfigs and StringResources.

I want to reimport our products using a different combination of Categories and Departments but I have heard that it is best not to use the Excel import to do updates.

The site is not live yet so I don't have to worry about customer data.

In the past I have just restored a clean database but I have spent alot of time configuring the AppConfigs etc and don't want to do that all over again.

Thank you for your help!!!!!

This is for the v8.0.1.2 database schema.

This is a VERY dangerous script. It will obliterate your data. Make sure you run it on a dev/test database.

I have commented out certain tables.

Code:

TRUNCATE TABLE [Address]
TRUNCATE TABLE [Affiliate]
TRUNCATE TABLE [AffiliateActivity]
TRUNCATE TABLE [AffiliateActivityReason]
TRUNCATE TABLE [AffiliateCommissions]
--TRUNCATE TABLE [AppConfig]
TRUNCATE TABLE [AuditLog]
TRUNCATE TABLE [BadWord]
TRUNCATE TABLE [Category]
TRUNCATE TABLE [ClickTrack]
TRUNCATE TABLE [Country]
TRUNCATE TABLE [CountryTaxRate]
TRUNCATE TABLE [Coupon]
TRUNCATE TABLE [CouponUsage]
TRUNCATE TABLE [CreditCardType]
TRUNCATE TABLE [Currency]
TRUNCATE TABLE [CustomCart]
--TRUNCATE TABLE [Customer]
TRUNCATE TABLE [CustomerGiftRegistrySearches]
TRUNCATE TABLE [CustomerLevel]
TRUNCATE TABLE [CustomerSession]
TRUNCATE TABLE [CustomReport]
TRUNCATE TABLE [Distributor]
TRUNCATE TABLE [Document]
TRUNCATE TABLE [DocumentAffiliate]
TRUNCATE TABLE [DocumentCustomerLevel]
TRUNCATE TABLE [DocumentLibrary]
TRUNCATE TABLE [DocumentType]
TRUNCATE TABLE [ErrorLog]
TRUNCATE TABLE [EventHandler]
TRUNCATE TABLE [ExtendedPrice]
TRUNCATE TABLE [FailedTransaction]
TRUNCATE TABLE [FAQ]
TRUNCATE TABLE [Feed]
TRUNCATE TABLE [Gallery]
TRUNCATE TABLE [Genre]
TRUNCATE TABLE [GiftCard]
TRUNCATE TABLE [GiftCardUsage]
TRUNCATE TABLE [Inventory]
TRUNCATE TABLE [KitCart]
TRUNCATE TABLE [KitGroup]
TRUNCATE TABLE [KitGroupType]
TRUNCATE TABLE [KitItem]
TRUNCATE TABLE [Library]
TRUNCATE TABLE [LocaleSetting]
TRUNCATE TABLE [LOG_CustomerEvent]
TRUNCATE TABLE [LOG_Event]
TRUNCATE TABLE [MailingMgrLog]
TRUNCATE TABLE [Manufacturer]
TRUNCATE TABLE [News]
TRUNCATE TABLE [OrderNumbers]
TRUNCATE TABLE [OrderOption]
TRUNCATE TABLE [Orders]
TRUNCATE TABLE [Orders_CustomCart]
TRUNCATE TABLE [Orders_KitCart]
TRUNCATE TABLE [Orders_ShoppingCart]
TRUNCATE TABLE [Partner]
TRUNCATE TABLE [PasswordLog]
TRUNCATE TABLE [Poll]
TRUNCATE TABLE [PollAnswer]
TRUNCATE TABLE [PollCategory]
TRUNCATE TABLE [PollSection]
TRUNCATE TABLE [PollSortOrder]
TRUNCATE TABLE [PollVotingRecord]
TRUNCATE TABLE [Product]
TRUNCATE TABLE [ProductAffiliate]
TRUNCATE TABLE [ProductCategory]
TRUNCATE TABLE [ProductCustomerLevel]
TRUNCATE TABLE [ProductDistributor]
TRUNCATE TABLE [ProductGenre]
TRUNCATE TABLE [ProductLocaleSetting]
TRUNCATE TABLE [ProductManufacturer]
TRUNCATE TABLE [ProductSection]
TRUNCATE TABLE [ProductType]
TRUNCATE TABLE [ProductVariant]
TRUNCATE TABLE [ProductVector]
TRUNCATE TABLE [ProductView]
TRUNCATE TABLE [QuantityDiscount]
TRUNCATE TABLE [QuantityDiscountTable]
TRUNCATE TABLE [Rating]
TRUNCATE TABLE [RatingCommentHelpfulness]
TRUNCATE TABLE [RestrictedIP]
--TRUNCATE TABLE [SalesPrompt]
TRUNCATE TABLE [SearchLog]
TRUNCATE TABLE [Section]
TRUNCATE TABLE [SecurityLog]
TRUNCATE TABLE [ShippingByProduct]
TRUNCATE TABLE [ShippingByTotal]
TRUNCATE TABLE [ShippingByTotalByPercent]
TRUNCATE TABLE [ShippingByWeight]
TRUNCATE TABLE [ShippingCalculation]
TRUNCATE TABLE [ShippingImportExport]
TRUNCATE TABLE [ShippingMethod]
TRUNCATE TABLE [ShippingMethodToCountryMap]
TRUNCATE TABLE [ShippingMethodToStateMap]
TRUNCATE TABLE [ShippingMethodToZoneMap]
TRUNCATE TABLE [ShippingTotalByZone]
TRUNCATE TABLE [ShippingWeightByZone]
TRUNCATE TABLE [ShippingZone]
TRUNCATE TABLE [ShoppingCart]
--TRUNCATE TABLE [SkinPreview]
TRUNCATE TABLE [SQLLog]
TRUNCATE TABLE [Staff]
TRUNCATE TABLE [State]
TRUNCATE TABLE [StateTaxRate]
--TRUNCATE TABLE [StringResource]
TRUNCATE TABLE [TaxClass]
TRUNCATE TABLE [Topic]
TRUNCATE TABLE [Vector]
TRUNCATE TABLE [ZipTaxRate]

If you run this and find that you've removed the wrong data, then you'll need to restore it from the database creation script from the ASPDotNetStorefront installation.

You should take a backup of the database before running this.

YOU HAVE BEEN WARNED.

Thank you for the help and the warning