Script scans your tables and create new categories and products (copy pictures and attributes)
Script doesn't create new attributes.
You can change selected values
We wrote this script for version 3.00. For version 2.80 you must delete some lines, read comments in the script.
exec us_FoxNetSoft_Create_DemoData
@kol_category_root=10, -- count new root categories
@kol_category=200, -- count new categories
@kol_product=30000 -- count new products
CREATE PROCEDURE [dbo].[us_FoxNetSoft_Create_Category]
@Id int,
@ParentCategoryId int
AS
set nocount on
declare @picture_id int,@categoryid int
--Create new Picture ID
select @picture_id=PictureId from Category WITH (NOLOCK) where id=@id
insert into Picture ([PictureBinary],[MimeType],[SeoFilename],[IsNew])
select [PictureBinary],[MimeType],[SeoFilename],[IsNew]
FROM [Picture]
where id=@picture_id
set @picture_id=SCOPE_IDENTITY( )
--LimitedToStores for 3.00
insert into [Category] ( [Name] ,
[Description],[CategoryTemplateId],[MetaKeywords],[MetaDescription]
,[MetaTitle],[ParentCategoryId],[PictureId],[PageSize],[AllowCustomersToSelectPageSize]
,[PageSizeOptions],[PriceRanges],[ShowOnHomePage],[HasDiscountsApplied]
,[SubjectToAcl],[LimitedToStores],[Published],[Deleted],[DisplayOrder],[CreatedOnUtc],[UpdatedOnUtc])
select ltrim(rtrim([Name]))+' Cat from Id='+ltrim(rtrim(str(@id))) as Name,
[Description] ,[CategoryTemplateId],[MetaKeywords],[MetaDescription]
,[MetaTitle] ,
@ParentCategoryId as ParentCategoryId ,@picture_id as PictureId ,[PageSize] ,[AllowCustomersToSelectPageSize]
,[PageSizeOptions],[PriceRanges],[ShowOnHomePage],[HasDiscountsApplied]
,[SubjectToAcl] ,[LimitedToStores],[Published],[Deleted],[DisplayOrder] ,[CreatedOnUtc],[UpdatedOnUtc]
FROM [Category]
where id=@id
set @categoryid=SCOPE_IDENTITY( )
insert UrlRecord ([EntityId]
,[EntityName]
,[Slug]
,[IsActive]
,[LanguageId])
select @categoryid
,[EntityName]
,ltrim(rtrim(Slug))+'-cat-Id-'+ltrim(rtrim(str(@categoryid))) as Slug
,[IsActive]
,[LanguageId]
from UrlRecord
where EntityId=@id and EntityName='Category'
GO
CREATE PROCEDURE [dbo].[us_FoxNetSoft_Create_Product]
@ProductIdFrom int,
@ParentCategoryId int
AS
set nocount on
declare @ProductId int
set @ProductId=0
--create new Product
--3.00 column name 'VendorId','LimitedToStores'
insert into [Product] (
[Name]
,[ShortDescription]
,[FullDescription]
,[AdminComment]
,[ProductTemplateId]
,[VendorId]
,[ShowOnHomePage]
,[MetaKeywords]
,[MetaDescription]
,[MetaTitle]
,[AllowCustomerReviews]
,[ApprovedRatingSum]
,[NotApprovedRatingSum]
,[ApprovedTotalReviews]
,[NotApprovedTotalReviews]
,[SubjectToAcl]
,[LimitedToStores]
,[Published]
,[Deleted]
,[CreatedOnUtc]
,[UpdatedOnUtc])
select ltrim(rtrim([Name]))+' prod from Id='+ltrim(rtrim(str(@ProductIdFrom))) as Name
,[ShortDescription]
,[FullDescription]
,[AdminComment]
,[ProductTemplateId]
,[VendorId]
,0 as [ShowOnHomePage]
,[MetaKeywords]
,[MetaDescription]
,[MetaTitle]
,[AllowCustomerReviews]
,[ApprovedRatingSum]
,[NotApprovedRatingSum]
,[ApprovedTotalReviews]
,[NotApprovedTotalReviews]
,[SubjectToAcl]
,[LimitedToStores]
,[Published]
,[Deleted]
,[CreatedOnUtc]
,[UpdatedOnUtc]
FROM [Product]
where [ID]=@ProductIdFrom
set @ProductId=SCOPE_IDENTITY( )
if @ProductId!=0
begin
insert UrlRecord ([EntityId]
,[EntityName]
,[Slug]
,[IsActive]
,[LanguageId])
select @ProductId
,[EntityName]
,ltrim(rtrim(Slug))+'-prod-Id-'+ltrim(rtrim(str(@ProductId))) as Slug
,[IsActive]
,[LanguageId]
from UrlRecord
where EntityId=@ProductIdFrom and EntityName='Product'
declare @PictureId int,@id int
--Picture
DECLARE view_product_picturies INSENSITIVE CURSOR
FOR SELECT id,PictureId
FROM Product_Picture_Mapping WITH (NOLOCK)
where ProductId=@ProductIdFrom
FOR READ ONLY
OPEN view_product_picturies
FETCH NEXT FROM view_product_picturies into @id,@PictureId
WHILE @@Fetch_Status=0
begin
insert into Picture ([PictureBinary],[MimeType],[SeoFilename],[IsNew])
select [PictureBinary],[MimeType],[SeoFilename],[IsNew]
FROM [Picture]
where id=@PictureId
set @PictureId=SCOPE_IDENTITY( )
insert into Product_Picture_Mapping (ProductId,PictureId,DisplayOrder)
select @ProductId,@PictureId,DisplayOrder
from Product_Picture_Mapping
where id=@id
FETCH NEXT FROM view_product_picturies into @id,@PictureId
end
DEALLOCATE view_product_picturies
--Category
Insert Product_Category_Mapping ([ProductId]
,[CategoryId]
,[IsFeaturedProduct]
,[DisplayOrder])
values (@ProductId,@ParentCategoryId,0,0)
/*SELECT @ProductId as ProductId
,@ParentCategoryId as CategoryId
,[IsFeaturedProduct]
,[DisplayOrder]
FROM [nop300].[dbo].[Product_Category_Mapping]
where ProductId=@ProductIdFrom*/
--Product_ProductTag_Mapping
insert into Product_ProductTag_Mapping (Product_Id,ProductTag_Id)
SELECT @ProductId as [Product_Id],[ProductTag_Id]
FROM [Product_ProductTag_Mapping]
where Product_Id=@ProductIdFrom
--Product_SpecificationAttribute_Mapping
insert into Product_SpecificationAttribute_Mapping ([ProductId]
,[SpecificationAttributeOptionId]
,[CustomValue]
,[AllowFiltering]
,[ShowOnProductPage]
,[DisplayOrder])
SELECT @ProductId as [ProductId]
,[SpecificationAttributeOptionId]
,[CustomValue]
,[AllowFiltering]
,[ShowOnProductPage]
,[DisplayOrder]
FROM [Product_SpecificationAttribute_Mapping]
where [ProductId]=@ProductIdFrom
--ProductVariant
declare @productvariantid int
DECLARE view_product_variant INSENSITIVE CURSOR
FOR SELECT id
FROM ProductVariant WITH (NOLOCK)
where ProductId=@ProductIdFrom
FOR READ ONLY
OPEN view_product_variant
FETCH NEXT FROM view_product_variant into @productvariantid
WHILE @@Fetch_Status=0
begin
insert into ProductVariant ([ProductId]
,[Name]
,[Sku]
,[Description]
,[AdminComment]
,[ManufacturerPartNumber]
,[Gtin]
,[IsGiftCard]
,[GiftCardTypeId]
,[RequireOtherProducts]
,[RequiredProductVariantIds]
,[AutomaticallyAddRequiredProductVariants]
,[IsDownload]
,[DownloadId]
,[UnlimitedDownloads]
,[MaxNumberOfDownloads]
,[DownloadExpirationDays]
,[DownloadActivationTypeId]
,[HasSampleDownload]
,[SampleDownloadId]
,[HasUserAgreement]
,[UserAgreementText]
,[IsRecurring]
,[RecurringCycleLength]
,[RecurringCyclePeriodId]
,[RecurringTotalCycles]
,[IsShipEnabled]
,[IsFreeShipping]
,[AdditionalShippingCharge]
,[IsTaxExempt]
,[TaxCategoryId]
,[ManageInventoryMethodId]
,[StockQuantity]
,[DisplayStockAvailability]
,[DisplayStockQuantity]
,[MinStockQuantity]
,[LowStockActivityId]
,[NotifyAdminForQuantityBelow]
,[BackorderModeId]
,[AllowBackInStockSubscriptions]
,[OrderMinimumQuantity]
,[OrderMaximumQuantity]
,[AllowedQuantities]
,[DisableBuyButton]
,[DisableWishlistButton]
,[AvailableForPreOrder]
,[CallForPrice]
,[Price]
,[OldPrice]
,[ProductCost]
,[SpecialPrice]
,[SpecialPriceStartDateTimeUtc]
,[SpecialPriceEndDateTimeUtc]
,[CustomerEntersPrice]
,[MinimumCustomerEnteredPrice]
,[MaximumCustomerEnteredPrice]
,[HasTierPrices]
,[HasDiscountsApplied]
,[Weight]
,[Length]
,[Width]
,[Height]
,[PictureId]
,[AvailableStartDateTimeUtc]
,[AvailableEndDateTimeUtc]
,[Published]
,[Deleted]
,[DisplayOrder]
,[CreatedOnUtc]
,[UpdatedOnUtc])
select @ProductId as [ProductId]
,[Name]
,[Sku]
,[Description]
,[AdminComment]
,[ManufacturerPartNumber]
,[Gtin]
,[IsGiftCard]
,[GiftCardTypeId]
,[RequireOtherProducts]
,[RequiredProductVariantIds]
,[AutomaticallyAddRequiredProductVariants]
,[IsDownload]
,[DownloadId]
,[UnlimitedDownloads]
,[MaxNumberOfDownloads]
,[DownloadExpirationDays]
,[DownloadActivationTypeId]
,[HasSampleDownload]
,[SampleDownloadId]
,[HasUserAgreement]
,[UserAgreementText]
,[IsRecurring]
,[RecurringCycleLength]
,[RecurringCyclePeriodId]
,[RecurringTotalCycles]
,[IsShipEnabled]
,[IsFreeShipping]
,[AdditionalShippingCharge]
,[IsTaxExempt]
,[TaxCategoryId]
,[ManageInventoryMethodId]
,[StockQuantity]
,[DisplayStockAvailability]
,[DisplayStockQuantity]
,[MinStockQuantity]
,[LowStockActivityId]
,[NotifyAdminForQuantityBelow]
,[BackorderModeId]
,[AllowBackInStockSubscriptions]
,[OrderMinimumQuantity]
,[OrderMaximumQuantity]
,[AllowedQuantities]
,[DisableBuyButton]
,[DisableWishlistButton]
,[AvailableForPreOrder]
,[CallForPrice]
,[Price]
,[OldPrice]
,[ProductCost]
,[SpecialPrice]
,[SpecialPriceStartDateTimeUtc]
,[SpecialPriceEndDateTimeUtc]
,[CustomerEntersPrice]
,[MinimumCustomerEnteredPrice]
,[MaximumCustomerEnteredPrice]
,[HasTierPrices]
,[HasDiscountsApplied]
,[Weight]
,[Length]
,[Width]
,[Height]
,0 as [PictureId]
,[AvailableStartDateTimeUtc]
,[AvailableEndDateTimeUtc]
,[Published]
,[Deleted]
,[DisplayOrder]
,[CreatedOnUtc]
,[UpdatedOnUtc]
FROM ProductVariant
where id=@productvariantid
set @id=SCOPE_IDENTITY( )
insert into ProductVariant_ProductAttribute_Mapping ([ProductVariantId]
,[ProductAttributeId]
,[TextPrompt]
,[IsRequired]
,[AttributeControlTypeId]
,[DisplayOrder])
select @id as [ProductVariantId]
,[ProductAttributeId]
,[TextPrompt]
,[IsRequired]
,[AttributeControlTypeId]
,[DisplayOrder]
from ProductVariant_ProductAttribute_Mapping
where id=@productvariantid
FETCH NEXT FROM view_product_variant into @productvariantid
end
DEALLOCATE view_product_variant
end
GO
CREATE PROCEDURE [dbo].[us_FoxNetSoft_Create_DemoData]
@kol_category_root int=20,
@kol_category int=500,
@kol_product int=3000
AS
set nocount on
declare @i int,@i2 int,@id int,@ParentCategoryId int
--insert root categories
set @i=0
set @i2=0
--LimitedToStores for 3.00
while (@i<@kol_category_root)
begin
DECLARE view_category_root INSENSITIVE CURSOR
FOR SELECT id
FROM Category WITH (NOLOCK)
where ParentCategoryId=0
FOR READ ONLY
OPEN view_category_root
FETCH NEXT FROM view_category_root into @id
WHILE @@Fetch_Status=0
begin
exec us_FoxNetSoft_Create_Category @id,0
set @i=@i+1
if @i>@kol_category_root
break
FETCH NEXT FROM view_category_root into @id
end
DEALLOCATE view_category_root
end
--insert categories
set @i=0
--LimitedToStores for 3.00
while (@i<@kol_category)
begin
DECLARE view_category INSENSITIVE CURSOR
FOR SELECT id
FROM Category WITH (NOLOCK)
where [Name] like '%from Id=%'
FOR READ ONLY
OPEN view_category
FETCH NEXT FROM view_category into @ParentCategoryId
WHILE @@Fetch_Status=0
begin
set @i2=0
--scan for all categories
DECLARE view_category2 INSENSITIVE CURSOR
FOR SELECT id
FROM Category WITH (NOLOCK)
FOR READ ONLY
OPEN view_category2
FETCH NEXT FROM view_category2 into @Id
WHILE @@Fetch_Status=0
begin
exec us_FoxNetSoft_Create_Category @id,@ParentCategoryId
set @i2=@i2+1
set @i=@i+1
if @i2>@kol_category_root
break
if @i>@kol_category
break
FETCH NEXT FROM view_category2 into @id
end
DEALLOCATE view_category2
if @i>@kol_category
break
FETCH NEXT FROM view_category into @ParentCategoryId
end
DEALLOCATE view_category
end
--CREATE PRODUCTS
set @i=0
--LimitedToStores for 3.00
while (@i<@kol_product)
begin
--scan for all products
DECLARE view_product INSENSITIVE CURSOR
FOR SELECT id
FROM Product WITH (NOLOCK)
FOR READ ONLY
OPEN view_product
FETCH NEXT FROM view_product into @Id
WHILE @@Fetch_Status=0
begin
DECLARE view_categoryp INSENSITIVE CURSOR
FOR SELECT id
FROM Category WITH (NOLOCK)
where [Name] like '%from Id=%' and ParentCategoryId!=0
FOR READ ONLY
OPEN view_categoryp
FETCH NEXT FROM view_categoryp into @ParentCategoryId
WHILE @@Fetch_Status=0
begin
exec us_FoxNetSoft_Create_Product @id,@ParentCategoryId
set @i=@i+1
if @i>@kol_product
break
FETCH NEXT FROM view_categoryp into @ParentCategoryId
end
DEALLOCATE view_categoryp
if @i>@kol_product
break
FETCH NEXT FROM view_product into @id
end
DEALLOCATE view_product
END
GO
exec us_FoxNetSoft_Create_DemoData
@kol_category_root=10,
@kol_category=200,
@kol_product=30000
GO
drop procedure us_FoxNetSoft_Create_Category
drop procedure us_FoxNetSoft_Create_Product
drop procedure us_FoxNetSoft_Create_DemoData
GO
