Tzael wrote:Here's what I use to reset most things. The second batch of commands resets the seed for the identity column. This doesn't affect customer data but you can follow the pattern for other tables if you wish. Alternatively you could drop and recreate the tables but then you have to make sure you reestablish foreign keys and the likes.
Worth mentioning that this probably isn't best for the customer table, you don't want to inadvertently delete the admin account! Modify the delete statement appropriately to exclude the admin account(s) you wish to preserve, and set the new seed value appropriately based on the highest identity value for any admin accounts you are preserving.
DELETE FROM Nop_ShoppingCartItem
DELETE FROM Nop_OrderNote
DELETE FROM Nop_OrderProductVariant
DELETE FROM Nop_Order
DELETE FROM Nop_ProductVariantAttributeCombination
DELETE FROM Nop_RelatedProduct
DELETE FROM Nop_ProductVariantAttributeValue
DELETE FROM Nop_ProductVariant_ProductAttribute_Mapping
DELETE FROM Nop_ProductAttribute
DELETE FROM Nop_ProductVariant
DELETE FROM Nop_ProductPicture
DELETE FROM Nop_Product_Category_Mapping
DELETE FROM Nop_Product
DELETE FROM Nop_Category
DELETE FROM Nop_Picture
DBCC CHECKIDENT (Nop_ShoppingCartItem, RESEED, 0)
DBCC CHECKIDENT (Nop_OrderNote, RESEED, 0)
DBCC CHECKIDENT (Nop_OrderProductVariant, RESEED, 0)
DBCC CHECKIDENT (Nop_Order, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductVariantAttributeCombination, RESEED, 0)
DBCC CHECKIDENT (Nop_RelatedProduct, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductVariantAttributeValue, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductVariant_ProductAttribute_Mapping, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductAttribute, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductVariant, RESEED, 0)
DBCC CHECKIDENT (Nop_ProductPicture, RESEED, 0)
DBCC CHECKIDENT (Nop_Product_Category_Mapping, RESEED, 0)
DBCC CHECKIDENT (Nop_Product, RESEED, 0)
DBCC CHECKIDENT (Nop_Category, RESEED, 0)
DBCC CHECKIDENT (Nop_Picture, RESEED, 0)
I just used your example and also included some TSQL to rebuild the indexes on all the effected tables:
DBCC DBREINDEX (Nop_ShoppingCartItem, " ")
DBCC DBREINDEX (Nop_ProductVariantAttributeCombination, " ")
DBCC DBREINDEX (Nop_ProductVariantAttributeValue, " ")
DBCC DBREINDEX (Nop_ProductVariant_ProductAttribute_Mapping, " ")
DBCC DBREINDEX (Nop_ProductAttribute, " ")
DBCC DBREINDEX (Nop_ProductVariant, " ")
DBCC DBREINDEX (Nop_Product, " ")
DBCC DBREINDEX (Nop_ProductPicture, " ")
DBCC DBREINDEX (Nop_Product, " ")
DBCC DBREINDEX (Nop_Category, " ")
DBCC DBREINDEX (Nop_Picture, " ")
DBCC DBREINDEX (Nop_Manufacturer, " ")
But SQL is now complaining about a FOREIGN KEY constraint when adding products! See below, any help greatly apprcieated!
ERROR: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Nop_ProductVariant_ProductAttribute_Mapping_Nop_ProductAttribute". The conflict occurred in database "surfari-nop18", table "dbo.Nop_ProductAttribute", column 'ProductAttributeID'.
The statement has been terminated.