Insert multiple rows with a single INSERT statement

If you want to insert multiple rows in one table with a single INSERT statement do the following:

INSERT ALL INTO testTable(col1,col2,col3) VALUES(1,2,3)
           INTO testTable(col1,col2,col3) VALUES(4,5,6)
           INTO testTable(col1,col2,col3) VALUES(7,8,9)
           INTO testTable(col1,col2,col3) VALUES(10,11,12)
SELECT * FROM DUAL;
COMMIT;

If you want to insert multiple rows in multiple tables with a single insert statement do the following:

INSERT ALL INTO testTable_1(col1,col2,col3) VALUES(1,2,3)
           INTO testTable_2(col1,col2,col3) VALUES(4,5,6)
           INTO testTable_3(col1,col2,col3) VALUES(7,8,9)
           INTO testTable_4(col1,col2,col3) VALUES(10,11,12)
SELECT * FROM DUAL;
COMMIT;

You also are able to use when to insert specific values into tables, so adding some condition:

INSERT FIRST
   WHEN a < 50 THEN
       INTO testTable_1 VALUES(a,b,c)
   WHEN a > 50 AND a < 500 THEN
       INTO testTable_2 VALUES(a,b,c)   
   WHEN a > 500 AND a < 1000 THEN
       INTO testTable_3 VALUES(a,b,c)
   WHEN a > 1000  THEN
       INTO testTable_4 VALUES(a, b, c)
SELECT col1 AS a, col2 AS b, col3 AS c
FROM testTable_5
COMMIT;

FIRST means that if one of the condition is satisfied Oracle will stop checking other conditions. For example, if after selecting first row from the table  testTable_5 the condition
a > 50 AND a < 500 is satisfied, Oracle will insert just into testTable_2 and go again to the SELECT statement for selecting another row, till the end of table.

INSERT statement has another option ALL which will check each WHEN clause doesn’t matter if any condition is already satisfied or not.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: