Importing Products via SQL

Does someone have a Insert statement that works for adding products?

How about looking in \Install\Scripts\nopCommerce_createSampleData.sql

I can honestly say I did, also tried the stored procedure ProductInsert
And both methods add a product to the Nop_Product table, but I must be missing a relationship or requirement somewhere, when either method is used the added product does not show up in the admin interface.

Instead of asking for the whole TSQL statement, could someone tell me which tables are required in order to do a complete product insert so it shows in the admin interface?

Thanks

All the answers are within that sql script.  You probably need to look at the product variant table also.

add one product manually.  Then add one via script and compare the differences.

Your right the products require a variant, that fixed me up.

Thanks

I have about 400 products currently and am importing more until I've done them all (approx 2000).  I bulk import my products from a bespoke excel spreadsheet in which I manage the prices, discounts, tax, margin etc.  I do about 20 or 30 each day.

I run the imports in 2 stages (just because I haven't used the correct SP which will create the product and then its variant).  example below if for a radio.

Stage 1 - run the following statement into the database to create the product(s) - this is from my excel spredsheet which concatenates the various fields for each product (1 statement per product, so I usually run multiples of these in at a time):

DECLARE @return_value int, @ProductID int EXEC @return_value = [dbo].[Nop_ProductInsert]
@ProductID = @ProductID OUTPUT,@Name = N'IC-M411Euro Compact Waterproof Fixed DSC VHF',@ShortDescription =N'',@FullDescription =N'',@AdminComment =N'',@ProductTypeID =1,@TemplateID =4,@ShowOnHomePage =False,@MetaKeywords = N'IC-M411Euro Compact Waterproof Fixed DSC VHF ICOM M411EURO',@MetaDescription =N'',@MetaTitle = N'IC-M411Euro Compact Waterproof Fixed DSC VHF',@SEName = N'ICM411Euro Compact Waterproof Fixed DSC VHF',@AllowCustomerReviews =True,@AllowCustomerRatings =True,@RatingSum =0,@TotalRatingVotes =0,@Published =True,@Deleted =False,@CreatedOn = '12/22/2009',@UpdatedOn = '12/22/2009'
SELECT @ProductID as N'@ProductID'
SELECT 'Return Value' = @return_value
GO

the output from this is the ProductID which I then place in my spreadsheet for the relevant proudct.

Stage 2 - from another field in my spreadsheet which concatenates the product fields (inc the ProductID entered from stage 1), I copy the following statement(s) into a query and run it in to the database:

INSERT INTO Nop_ProductVariant (ProductId, [Name],SKU,[Description],AdminComment,ManufacturerPartNumber,IsDownload,DownloadID,IsShipEnabled,IsFreeShipping,AdditionalShippingCharge,IsTaxExempt,TaxCategoryID,ManageInventory,StockQuantity,MinStockQuantity,LowStockActivityID,NotifyAdminForQuantityBelow,OrderMinimumQuantity,OrderMaximumQuantity,WarehouseId,DisableBuyButton,RequiresTextOption,TextOptionPrompt,Price,OldPrice,Weight,[Length],Width,Height,PictureID,AvailableStartDateTime,AvailableEndDateTime,Published,Deleted,DisplayOrder,CreatedOn,UpdatedOn) VALUES (316,'','M411EURO','','','M411EURO',0,0,1,0,0,0,0,1,1000,0,0,1,1,10000,0,0,0,'',132.9018859,0,1,0,0,0,0,NULL,NULL,1,0,1,GETDATE(),GETDATE())

That's the bulk of the product information inserted - I just then go and manually map manufactuers and category, add pictures and any description though all this could be done by script if I could be bothered to get round to it.

Takes a few minutes to bulk import lots of products this way once you have your spreadsheet or source created and producing the right scripts.