Inserting Multiple rows into a single table.

[fawkman]

Hey guys

I am Using Oracle APex and have created a some tables running SQL commands. I am now trying to INSERT multiple rows of data into one of the tables but am running into issues.

I firstly tried the following:

INSERT INTO stock_item_table (stock_item_no, item_desc, item_price)
VALUES(11,'item1',12);
VALUES(12,'item1',22);
VALUES(13,'item1',32);
VALUES(14,'item1',42);

but this didn't work. and I got the following error: ORA-00911: invalid character

After reviewing the following Oracle thread on DaniWeb http://www.daniweb.com/forums/thread212239.html I created this script.

INSERT INTO stock_item_table (stock_item_no, item_desc, item_price)
SELECT 11 AS stock_item_no, 'item1' AS item_desc, 12 AS item_price FROM dual
UNION SELECT 12 AS stock_item_no, 'item1' AS item_desc, 22 AS item_price FROM dual
UNION SELECT 13 AS stock_item_no, 'item1' AS item_desc, 32 AS item_price FROM dual
UNION SELECT 14 AS stock_item_no, 'item1' AS item_desc, 42 AS item_price FROM dual
UNION SELECT 15 AS stock_item_no, 'item1' AS item_desc, 52 AS item_price FROM dual
UNION SELECT 16 AS stock_item_no, 'item1' AS item_desc, 62 AS item_price FROM dual
UNION SELECT 17 AS stock_item_no, 'item1' AS item_desc, 72 AS item_price FROM dual
UNION SELECT 18 AS stock_item_no, 'item1' AS item_desc, 82 AS item_price FROM dual
UNION SELECT 19 AS stock_item_no, 'item1' AS item_desc, 92 AS item_price FROM dual
UNION SELECT 20 AS stock_item_no, 'item1' AS item_desc, 112 AS item_price FROM dual
UNION SELECT 30 AS stock_item_no, 'item3' AS item_desc, 12 AS item_price FROM dual
UNION SELECT 31 AS stock_item_no, 'item3' AS item_desc, 22 AS item_price FROM dual
UNION SELECT 32 AS stock_item_no, 'item3' AS item_desc, 32 AS item_price FROM dual
UNION SELECT 34 AS stock_item_no, 'item3' AS item_desc, 42 AS item_price FROM dual
UNION SELECT 35 AS stock_item_no, 'item3' AS item_desc, 52 AS item_price FROM dual
UNION SELECT 36 AS stock_item_no, 'item3' AS item_desc, 62 AS item_price FROM dual
UNION SELECT 37 AS stock_item_no, 'item3' AS item_desc, 72 AS item_price FROM dual
UNION SELECT 38 AS stock_item_no, 'item3' AS item_desc, 82 AS item_price FROM dual
UNION SELECT 39 AS stock_item_no, 'item3' AS item_desc, 92 AS item_price FROM dual
UNION SELECT 50 AS stock_item_no, 'item5' AS item_desc, 12 AS item_price FROM dual
UNION SELECT 51 AS stock_item_no, 'item5' AS item_desc, 12 AS item_price FROM dual
UNION SELECT 52 AS stock_item_no, 'item5' AS item_desc, 22 AS item_price FROM dual
UNION SELECT 53 AS stock_item_no, 'item5' AS item_desc, 32 AS item_price FROM dual
UNION SELECT 54 AS stock_item_no, 'item5' AS item_desc, 42 AS item_price FROM dual
UNION SELECT 55 AS stock_item_no, 'item5' AS item_desc, 52 AS item_price FROM dual
UNION SELECT 56 AS stock_item_no, 'item5' AS item_desc, 62 AS item_price FROM dual
UNION SELECT 57 AS stock_item_no, 'item5' AS item_desc, 72 AS item_price FROM dual
UNION SELECT 58 AS stock_item_no, 'item5' AS item_desc, 82 AS item_price FROM dual
UNION SELECT 59 AS stock_item_no, 'item5' AS item_desc, 92 AS item_price FROM dual
UNION SELECT 60 AS stock_item_no, 'item7' AS item_desc, 12 AS item_price FROM dual
UNION SELECT 61 AS stock_item_no, 'item7' AS item_desc, 12 AS item_price FROM dual
UNION SELECT 62 AS stock_item_no, 'item7' AS item_desc, 22 AS item_price FROM dual
UNION SELECT 63 AS stock_item_no, 'item7' AS item_desc, 32 AS item_price FROM dual
UNION SELECT 64 AS stock_item_no, 'item7' AS item_desc, 42 AS item_price FROM dual
UNION SELECT 65 AS stock_item_no, 'item7' AS item_desc, 52 AS item_price FROM dual
UNION SELECT 66 AS stock_item_no, 'item7' AS item_desc, 62 AS item_price FROM dual
UNION SELECT 67 AS stock_item_no, 'item7' AS item_desc, 72 AS item_price FROM dual
UNION SELECT 68 AS stock_item_no, 'item7' AS item_desc, 82 AS item_price FROM dual
UNION SELECT 69 AS stock_item_no, 'item7' AS item_desc, 92 AS item_price FROM dual;

I get the following error now: The requested URL /apex/wwv_flow.show was not found on this server

I am not sure what FROM dual is referencing, but should this be referencing the table I am inserting into? which in this case is stock_item_table? I do not have a table called dual. If anyone can help me out and explain what I have done wrong and what I need to change for this script to run I would be very grateful. Thanks for any ones time and help.

Kind regards

[debasisdas]

You need to use INSERT ALL.

[fawkman]

You need to use INSERT ALL.

HI debasisdas

Thanks for the reply to my post. I do not understand where I need to put INSERT ALL, i tried

INSERT ALL INTO stock_item_table (stock_item_no, item_desc, item_price)
SELECT 11 AS stock_item_no, 'item1' AS item_desc, 12 AS item_price FROM dual
UNION SELECT 12 AS stock_item_no, 'item1' AS item_desc, 22 AS item_price FROM dual
UNION SELECT 13 AS stock_item_no, 'item1' AS item_desc, 32 AS item_price FROM dual
UNION SELECT 14 AS stock_item_no, 'item1' AS item_desc, 42 AS item_price FROM dual
UNION SELECT 15 AS stock_item_no, 'item1' AS item_desc, 52 AS item_price FROM dual;

but i still get the same error.

Is it possible you could give me an example of how to use the INSERT ALL statement please. Thanks for your help

[debasisdas]

Have a look at this sample code

INSERT ALL
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date, sales_sun)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)

find more in details here

[fawkman]

Hi

Thanks very much for your reply, really appreciate your time. I will have a look at this and test the code with what I have. kind regards