Query won't return any results just the headers

Question

INSERT INTO Pricing_Compare (SKU, ScansourcePrice, BluestarPrice, BarcodePrice, Manufacturer, Product)    
   SELECT 
       Product.SKU, Product.Name, ProductVariant.Price, 
       Manufacturer.Name,Scansource.COST, Bluestar.PRICE
   FROM 
       Product 
   INNER JOIN 
       ProductVariant ON Product.ProductID = ProductVariant.ProductID 
   INNER JOIN 
       ProductManufacturer ON Product.ProductID = ProductManufacturer.ProductID 
   INNER JOIN 
       Manufacturer ON ProductManufacturer.ManufacturerID = Manufacturer.ManufacturerID
   INNER JOIN 
       Scansource ON Product.SKU =Scansource.SKU
   INNER JOIN 
       Bluestar ON Product.SKU = Bluestar.MFG_PART_NUMBER

I get 0 rows affected. This seems straight forward. Can't figure it out.

Comment out each of the inner joins one by one and run the select query - to see which of the joins is causing no results to be displayed. Then check that whatever table that is does indeed have the rows you think it does to allow it to match to your outer query. And if it doesn't but it doesn't need to, consider using a left join for that table instead of an inner join. — CustodianOfCode, Aug 19 '15 at 00:03
Tried the left Join and got: `Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'ProductID', table '9300_A.dbo.Pricing_Compare'; column does not allow nulls. INSERT fails. The statement has been terminated.` — Fred, Aug 19 '15 at 00:19
So it wants a productID in the Pricing_Compare table. Can you check what columns pricing_compare contains, and ensure that your select query is inserting all of these values? It looks like product ID is available, but you have not included it in the list of fields to be inserted into pricing_compare. — CustodianOfCode, Aug 19 '15 at 00:37

score 0 · Answer 1 · answered Aug 19 '15 at 00:09

0

Try this .I have rearranged the column names in the select query.

INSERT INTO Pricing_Compare (SKU,ScansourcePrice,BluestarPrice,BarcodePrice,Manufacturer,Product)    
SELECT Product.SKU,Scansource.COST, Bluestar.PRICE, ProductVariant.Price Manufacturer.Name,Product.Name
FROM Product 
INNER JOIN ProductVariant ON Product.ProductID = ProductVariant.ProductID 
INNER JOIN ProductManufacturer ON Product.ProductID = ProductManufacturer.ProductID 
INNER JOIN Manufacturer ON ProductManufacturer.ManufacturerID = Manufacturer.ManufacturerID
INNER JOIN Scansource ON Product.SKU =Scansource.SKU
INNER JOIN Bluestar ON Product.SKU = Bluestar.MFG_PART_NUMBER

answered Aug 19 '15 at 00:09

Praveen reddy Dandu

188
3
14

0 rows affected again. – Fred Aug 19 '15 at 00:14
how many results you are getting when you run select query alone? – Praveen reddy Dandu Aug 19 '15 at 00:20
`Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Product.SKU" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Product.Name" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "ProductVariant.Price" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Manufacturer.Name" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Scansource.COST" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Bluestar.PRICE" could not be bound.` – Fred Aug 19 '15 at 00:22
there is a thread with similar error at this link http://stackoverflow.com/questions/7314134/the-multi-part-identifier-could-not-be-bound – Praveen reddy Dandu Aug 19 '15 at 00:51

Query won't return any results just the headers

1 Answers1