0

My SQL code is not working I get 0 rows affected when my code is supposed to be correct.

use CA

truncate table salesFacts

INSERT INTO CA..salesFacts
(date,customerNumber,productCode,orderNumber,productLine,price,quantity)

SELECT
 replace(CONVERT(DATE,o.OrderDate, 112),'-',''),
 c.customerNumber,
 pr.productCode,
 o.orderNumber, 
 pl.productLine,
 od.priceEach,
 od.quantityOrdered



FROM

CA..orders o,
CA..products pr,
CA..customers c,  
CA..orderDetails od, 
CA..productLines pl


WHERE

o.customerNumber = c.customerNumber
AND od.productCode = pr.productCode
AND od.orderNumber = o.orderNumber
AND pr.productLine = pl.productLine

This is the table for it:

CREATE TABLE  salesFacts (   
date    Datetime    NOT NULL, 
customerNumber int  NOT NULL, 
productCode varchar(15) NOT NULL,
orderNumber int NOT NULL,
productLine varchar(50) NOT NULL,
price   decimal(6,2)    NOT NULL,
quantity    INT     NOT NULL,
primary key(customerNumber, productCode, orderNumber, productLine),
foreign key (customerNumber) references Customers (customerNumber),
foreign key(productCode) references products(productCode),
foreign key (orderNumber) references orders(orderNumber),
foreign key (productLine) references productLines(productLine))

Thank you so much for your help in advance. I have been straggling with this code for hours and still could not do it. Any help would be appreciated

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
codingboss
  • 1
  • try running only the select part . Does it return anything ? Are you sure about the `JOINS` ? – minatverma Jan 03 '16 at 17:54
  • It doesn't run when we only run the select part but if we run everything tgtr it runs but only 0 rows affected – codingboss Jan 03 '16 at 17:58
  • Understand `SELECT` queries are not action queries, so nothing is supposed to run but only return a resultset. As @minatverma suggests, if your select statement returns 0 rows, of course the `INSERT INTO ... SELECT` would result in 0 rows affected. Also, do be aware your query uses the older SQL syntax of implicit joins (using `WHERE` clauses) and not explicit joins using `INNER JOIN`. [Performance](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) is the same but readability/maintainability may not be. – Parfait Jan 03 '16 at 21:27
  • Is it safe to insert a string into the `date` column? – Turophile Jan 03 '16 at 21:33
  • The syntax is clearly SQL Server, so I removed the MySQL tag. – Gordon Linoff Jan 03 '16 at 21:46

0 Answers0