Multi-row insert in Oracle

Today's  discovery:

Inserting multi-rows   

There are various methods for this mentioned as under:

1. Using INSERT ALL:

The syntax for the this:

INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;
The syntax for INSERT ALL:
INSERT ALL
INTO <table_name> VALUES <column_name_list)
INTO <table_name> VALUES <column_name_list)
...
<SELECT Statement>;



Note:


If there is nothing we want to select after inserting we do select * from dual otherwise we do our select, we want usually to confirm the insert success.
The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'.


2. Using anonymous PL/SQL block:

Begin;
INSERT INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
INSERT INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
INSERT INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
commit;
end;

3. Using UNION ALL:


INSERT INTO table (column1, column2)
SELECT value1, value2 FROM DUAL UNION ALL
SELECT value1, value2 FROM DUAL UNION ALL
...etc...
SELECT value1, value2 FROM DUAL ;