Thread: Delete test products before going live

How can I delete all test products and related entities, etc. before going live?
Thanks.

This is normally something we do directly in SQL. Do you have access to this?

The tables you want to remove data from are:

Product
ProductVariant
ProductCategory
ProductDepartment
ProductSection

We normally just execute the following type of delete statement on them:

Delete From Product Where (condition for test products)

A sample from the recent http://www.shopdetails.ca site would be:

Delete From ProductDepartment Where ProductID IN
(
Select ProductId From Product Where Name like '%testing%'
)

Thanks, I do have direct access to the sql database I just wasn't sure of all of the affected tables -- thanks again for your quick reply

To do this, run the following SQL statements against your store database. This removes these products for good, there is no recovering them! Be sure you have a good backup first.

DELETE FROM Product
DELETE FROM ProductAffiliate
DELETE FROM ProductCategory
DELETE FROM ProductCustomerLevel
DELETE FROM ProductDistributor
DELETE FROM ProductGenre
DELETE FROM ProductLocaleSetting
DELETE FROM ProductManufacturer
DELETE FROM ProductSection
DELETE FROM ProductVariant
DELETE FROM ProductVector
DELETE FROM ShippingByProduct

Check if this works for you

Just to add to this, if you have already added products and just want to delete the ones from your test data, the test data product IDs are 1-202 so you can add a where clause to Melanie's queries: WHERE ProductID < 203

how would we reset the starting product id? The manual explains how to reset the order numbers but not the product id's. any help would be greatly appreciated! thanks!

whoops i have found the answer.

dbcc checkident(OrderNumbers, reseed, 1)

I thought OrderNumbers was a variable holding the count but I realized later that it is a table. So im assuming checkident checks and/or sets the autoincrementing primary key?

What about the test customers and orders? What tables should be cleared and is it safe to delete all entries in those tables?

The following are the tables needed to be cleared:

For Customer:
Customer

For Order:
OrderNumbers
Orders
Orders_CustomCart
Orders_KitCart
Orders_ShoppingCart

Can you tell me what tables to clear for categories, I want to reset the ID # back to 1 for my categories and products.

Thank you

Once you have deleted all your categories, run this query to reset back your CategoryID to 1:

Code:

dbcc checkident(Category, reseed, 0)

Check if this works for you

Actually, using TRUNCATE TABLE tablename, both removes all data and resets the identity counter to the seed at the same time.

It is faster than a delete statement which removes one row at a time, and the deletes are recorded in the transaction log.