I need to a script to delete all products from the DB and any association to them.

Is there any SQL script out there that will let me truly delete all products and their associations? I'm on nop 3.10.

I think these are all the tables associated with the Product table:

[MSSQLSERVER31].[dbo].[Product]
[MSSQLSERVER31].[dbo].[BackInStockSubscription]
[MSSQLSERVER31].[dbo].[Discount_AppliedToProducts]
[MSSQLSERVER31].[dbo].[OrderItem]
[MSSQLSERVER31].[dbo].[Product_Category_Mapping]
[MSSQLSERVER31].[dbo].[Product_Manufacturer_Mapping]
[MSSQLSERVER31].[dbo].[Product_Picture_Mapping]
[MSSQLSERVER31].[dbo].[Product_ProductAttribute_Mapping]
[MSSQLSERVER31].[dbo].[Product_ProductTag_Mapping]
[MSSQLSERVER31].[dbo].[Product_SpecificationAttribute_Mapping]
[MSSQLSERVER31].[dbo].[ProductReview]
[MSSQLSERVER31].[dbo].[ProductVariantAttributeCombination]
[MSSQLSERVER31].[dbo].[ShoppingCartItem]
[MSSQLSERVER31].[dbo].[TierPrice]

Is there a quick way to empty all these tables out?

TRUNCATE TABLE is the quickest way.

If you delete Product_Picture_Mapping, the Pictures will be orphaned.
If you delete OrderItem, the Order will still be there.  (OrderNote is also a child of Order)
(There may be others)

New York wrote:

I tried truncate table but it wouldn't let me due to foreign key constraints.

I ended up just manually deleting the products 200 at a time from the product table and it also deleted all it's association from other tables automatically.

Oh yeah, I forgot about the FKs.  (That might also handle deleting the pictures)

[code]I have been trying to remove products and have used the following to remove 
ALL PRODUCTS and ALL Associations - so it removes all ORDERS / PRODUCTS and a whole load of information - basically it gives a clean (of products and related data) database.  (NOP 3.3 only - not run on anything before that version)

It does NOT delete categories because  wanted to keep those - but it does delete any product relations to those categories

I am using this as i load products up via a spreadsheet and have had some issues getting it to load properly  so roll back and try again ! (using the excellent NOPADMIN - version 3.3)

I have tested and so far all seems ok - but one big warning 

BACK UP THE DATABASE BEFORE RUNNING THIS - just in case !

-------------------------------------------------

DELETE FROM ShoppingCartItem
DELETE FROM OrderNote
DELETE FROM RecurringPayment
DELETE FROM RewardPointsHistory
DELETE FROM [Order]
DELETE FROM ProductVariantAttributeCombination
DELETE FROM RelatedProduct
DELETE FROM ProductAttribute
DELETE FROM Picture
DELETE FROM Product_Category_Mapping
DELETE FROM Product

DELETE FROM ProductVariantAttributeCombination
DELETE FROM Product_Picture_Mapping
DELETE FROM Product_ProductAttribute_Mapping
DELETE FROM ProductVariantAttributeValue
DELETE FROM Product_ProductTag_Mapping
DELETE FROM Product_SpecificationAttribute_Mapping
DELETE FROM ProductVariantAttributeCombination
DELETE FROM ProductReview
DELETE FROM ProductReviewHelpfulness
Delete UrlRecord Where EntityName = 'Product'



DBCC CHECKIDENT (ShoppingCartItem, RESEED, 0)
DBCC CHECKIDENT (OrderNote, RESEED, 0)
DBCC CHECKIDENT (RecurringPayment, RESEED, 0)
DBCC CHECKIDENT (RewardPointsHistory, RESEED, 0)
DBCC CHECKIDENT ([Order], RESEED, 0)
DBCC CHECKIDENT (ProductVariantAttributeCombination, RESEED, 0)
DBCC CHECKIDENT (RelatedProduct, RESEED, 0)
DBCC CHECKIDENT (ProductVariantAttributeValue, RESEED, 0)

DBCC CHECKIDENT (ProductAttribute, RESEED, 0)

DBCC CHECKIDENT (Picture, RESEED, 0)
DBCC CHECKIDENT (Product_Category_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product, RESEED, 0)

DBCC CHECKIDENT ( ProductVariantAttributeCombination, RESEED, 0)
DBCC CHECKIDENT ( Product_Picture_Mapping, RESEED, 0)
DBCC CHECKIDENT ( Product_ProductAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (ProductVariantAttributeValue, RESEED, 0)

DBCC CHECKIDENT (Product_SpecificationAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (ProductVariantAttributeCombination, RESEED, 0)
DBCC CHECKIDENT ( ProductReview, RESEED, 0)
DBCC CHECKIDENT (ProductReviewHelpfulness, RESEED, 

0)



[/code]

I edited the above so it works
with version 3.70.


DELETE FROM ShoppingCartItem
DELETE FROM OrderNote
DELETE FROM RecurringPayment
DELETE FROM RewardPointsHistory
DELETE FROM [Order]
DELETE FROM PredefinedProductAttributeValue
DELETE FROM RelatedProduct
DELETE FROM ProductAttribute
DELETE FROM Picture
DELETE FROM Product_Category_Mapping
DELETE FROM Product
DELETE FROM PredefinedProductAttributeValue
DELETE FROM Product_Picture_Mapping
DELETE FROM Product_ProductAttribute_Mapping
DELETE FROM PredefinedProductAttributeValue
DELETE FROM Product_ProductTag_Mapping
DELETE FROM Product_SpecificationAttribute_Mapping
DELETE FROM ProductReview
DELETE FROM ProductReviewHelpfulness
Delete UrlRecord Where EntityName = 'Product'

DBCC CHECKIDENT (ShoppingCartItem, RESEED, 0)
DBCC CHECKIDENT (OrderNote, RESEED, 0)
DBCC CHECKIDENT (RecurringPayment, RESEED, 0)
DBCC CHECKIDENT (RewardPointsHistory, RESEED, 0)
DBCC CHECKIDENT ([Order], RESEED, 0)
DBCC CHECKIDENT (RelatedProduct, RESEED, 0)
DBCC CHECKIDENT (ProductAttribute, RESEED, 0)
DBCC CHECKIDENT (Picture, RESEED, 0)
DBCC CHECKIDENT (Product_Category_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product, RESEED, 0)
DBCC CHECKIDENT (PredefinedProductAttributeValue, RESEED, 0)
DBCC CHECKIDENT (Product_Picture_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product_ProductAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product_SpecificationAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (PredefinedProductAttributeValue, RESEED, 0)
DBCC CHECKIDENT (ProductReview, RESEED, 0)
DBCC CHECKIDENT (ProductReviewHelpfulness, RESEED,0)




Now if you also want to start clean and delete all Subcategories along with master category then



DELETE FROM Category;
DBCC CHECKIDENT (Category, RESEED, 0)


If you can't run the script because your hiding under the desk then use

http://sqlbackupandftp.com/sqltoexcel/
Sql to Excel it's free.

You can take out your SQL products table directly, and convert it to straight to MS excel (can't escape excel).Then you can remove certain duplicates if you messed up your SKUs then upload back to SQL using use SQL Server 20XX Import and Export Data (64-bit). Or you can use the admin panel of Nop. Either way
Have fun.

With Nopcommerce 3.8 this work for me

DELETE FROM ShoppingCartItem
DELETE FROM OrderNote
DELETE FROM RecurringPayment
DELETE FROM RewardPointsHistory
DELETE FROM [Order]
DELETE FROM PredefinedProductAttributeValue
DELETE FROM RelatedProduct
DELETE FROM ProductAttribute
DELETE FROM Picture
DELETE FROM Product_Category_Mapping
DELETE FROM Product
DELETE FROM PredefinedProductAttributeValue
DELETE FROM Product_Picture_Mapping
DELETE FROM Product_ProductAttribute_Mapping
DELETE FROM PredefinedProductAttributeValue
DELETE FROM Product_ProductTag_Mapping
DELETE FROM Product_SpecificationAttribute_Mapping
DELETE FROM ProductReview
DELETE FROM ProductReviewHelpfulness
DELETE FROM GiftCard
Delete UrlRecord Where EntityName = 'Product'

DBCC CHECKIDENT (ShoppingCartItem, RESEED, 0)
DBCC CHECKIDENT (OrderNote, RESEED, 0)
DBCC CHECKIDENT (RecurringPayment, RESEED, 0)
DBCC CHECKIDENT (RewardPointsHistory, RESEED, 0)
DBCC CHECKIDENT ([Order], RESEED, 0)
DBCC CHECKIDENT (RelatedProduct, RESEED, 0)
DBCC CHECKIDENT (ProductAttribute, RESEED, 0)
DBCC CHECKIDENT (Picture, RESEED, 0)
DBCC CHECKIDENT (Product_Category_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product, RESEED, 0)
DBCC CHECKIDENT (PredefinedProductAttributeValue, RESEED, 0)
DBCC CHECKIDENT (Product_Picture_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product_ProductAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (Product_SpecificationAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (PredefinedProductAttributeValue, RESEED, 0)
DBCC CHECKIDENT (ProductReview, RESEED, 0)
DBCC CHECKIDENT (ProductReviewHelpfulness, RESEED,0)

Does this also work on 3.9 version?

Is this script still ok for 4.1 ? Or does it need any changes ?
Thanks