Select from two different tables and store calculated value in a newly created table (Microsoft SQL Server 2014)

Question

What would be the best way to take values from two different tables like 1st table has the value of Price, where 2nd table has the value of Quantity, and I will multiply Price by Quantity and the calculated values, Total_Price which will be store in table 3 (newly created). At 1st I've tried using FUNCTION, but many error pops out, so I change it to CTE. But my teacher suggest me to not use temporary tables, because when new row data is added to the tables, we need to run the CTE again to update it everytime new record is added. Is there any other method? Thank you.

Answer 1

You can try something like (syntax not verified!):

INSERT INTO Table_3 (Cur_Date,Prod,Qty,Total_Price)
            VALUES  (GETDATE()               ,  
                     <the passed product_ID> ,
                     <the passed quantity>   ,
                     (SELECT (A.Quantity * B.Price) 
                        FROM Table_1 A ,
                             Table_2 B
                       WHERE A.Product = <Your passed product ID>
                         AND A.Product = B.Product
                     )
                    );

The actual phrasing will depend on your DBMS.