orical/PLSQL: insert all statement
This orical tutorial explain how to use INSERT STATEMENT with syntax and example
Description
This oeical tutorial explain how to add multiple value in one column using INSERT ALL statement
Syntax:
INSERT ALL INTO mytable (column 1,column 2 ,column n) values (expr1, expr2,expr_n) INTO mytable (column 1,column 2 ,column n) values (expr1, expr2,expr_n) INTO mytable (column 1,column 2 ,column n) values (expr1, expr2,expr_n) SELECT * FROM dual; |
Parameters or argument
Mytable
This table to insert the record into
Column 1 column 2 column_3
The column in the table to insert value
expr1, expr2,expr_n
The value to assign to the column in the table
EXAMPLE - insert into on table
You can use INSERT INTO statement to insert multiple record into one table for example if you want to insert 3 row in to supplier table you cod run the following statement
INSERT ALL INTO mytable (supplier_id, supplier_name) values (1000, ‘IBM’); INTO mytable (supplier_id, supplier_name) values (2000, ‘microsoft’);; INTO mytable (supplier_id, supplier_name) values (3000, ‘google’); SELECT * FROM dual; |
This equation to the following 3 insert statement
INSERT ALL INTO mytable (supplier_id, supplier_name) values (1000, ‘IBM’); INTO mytable (supplier_id, supplier_name) values (2000, ‘microsoft’);; INTO mytable (supplier_id, supplier_name) values (3000, ‘google’); SELECT * FROM dual; |
Example insert from multiple table
You can also use the INSERT ALL statement to insert multiple rows into more than one table in one command.
For example, if you wanted to insert into both the suppliers and customers table, you could run the following SQL statement:
INSERT ALL INTO mytable (supplier_id, supplier_name) values (1000, ‘IBM’); INTO mytable (supplier_id, supplier_name) values (2000, ‘microsoft’);; INTO mytable (supplier_id, supplier_name city) values (999999, ‘andeson construction’,’new york’); SELECT * FROM dual; |
This example will insert 2 rows into the suppliers table and 1 row into the customers table. It is equivalent to running these 3 INSERT statements:
INSERT ALL INTO mytable (supplier_id, supplier_name) values (1000, ‘IBM’); INTO mytable (supplier_id, supplier_name) values (2000, ‘microsoft’);; INTO mytable (supplier_id, supplier_name city) values (999999, ‘andeson construction’,’new york’); SELECT * FROM dual; |
0 Comments