I want to insert data from one table into an other while doing some math with it. What is the best way to do this?
I got 4 tables: “invoice”, “invoiceDetails”, “sales” and “products”
“invoiceDetails” is linked to the “products” sold (1 “id” on 1 “p” relation)
“invoice” is linked to “invoiceDetails” (1 “i” on many “id” relation)
I already have a nice report delivering a pretty invoice through sql and report functionality.
Now however i want to insert the “invoice” totals derived from “invoiceDetails” and “products” which are only calculated in the report into the “sales” table. What is the best way to do this? Using SQL of course (INSERT…INTO…) but how and when should I run this SQL?
project file is available
https://docs.google.com/open?id=0BzXci31E7oR8OXNMR3lsNDRxcnc