add multiple values in one column in Oracle

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;


Post a Comment

0 Comments