Oracle Multi-Table Insert Syntax? – This article will take you through the common SQL errors that you might encounter while working with sql, oracle, sql-insert. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I’m taking my first look at Oracle’s multi-table insert (I’m fairly new to SQL overall), and I’m not quite understanding the purpose/need for the SELECT at the end of the statement.
With a single-table INSERT, it’s my understanding that either the VALUES clause or a subquery are used, but not both. Can someone explain the significance of the SELECT clause at the end of this INSERT statement? I’ve looked online, but I haven’t found a clear answer.
INSERT ALL
WHEN prod_category='B' THEN
INTO book_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
WHEN prod_category='V' THEN
INTO video_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
WHEN prod_category='A' THEN
INTO audio_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
SELECT prod_category ,product_id ,customer_id ,sale_qty, sale_price
FROM sales_detail;
Solution :
The select is used to determine the values of the variable prod_category used in WHEN prod_category='B' THEN
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.