Inserting several rows in table

Question

I can insert one row in a table using this code:

INSERT INTO SCHEME.TABLE
    (col1, col2)
VALUES ('val1', 'val2');

I need to insert several rows in a table.

To insert several rows, i've tried:

INSERT INTO SCHEME.TABLE
    (col1, col2)
VALUES ('val1', 'val2'),
VALUES ('val1', 'val2');

INSERT INTO SCHEME.TABLE
    (col1, col2)
VALUES ('val1', 'val2'),('val1', 'val2');

and some other variations. Niether of them worked :(

As far as I remember in Oracle you cannot insert multiple literal rows. Just run multiple SQL `INSERT`s. Alternatively, you could use a subquery instead of `VALUES`. — The Impaler, Nov 29 '18 at 19:42
See https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems025.htm — The Impaler, Nov 29 '18 at 19:44
See the most upvoted (not the accepted) answer from the question I linked above — Aaron Dietz, Nov 29 '18 at 19:45
The most upvoted answer provides a longer SQL statement than multiple inserts combined. The silver lining is that it executes as a single statement. — The Impaler, Nov 29 '18 at 19:47

GMB · Accepted Answer · 2018-11-29T20:18:43.357

2

You need an INSERT statement for each row to insert, like :

INSERT INTO SCHEME.TABLE (col1, col2) VALUES ('val1', 'val2');
INSERT INTO SCHEME.TABLE (col1, col2) VALUES ('val3', 'val4');

Or you can use the INSERT ALL construct (but you still need to repeat the table name each time, and you need to finish your statement with some kind of select) :

INSERT ALL
    INTO SCHEME.TABLE (col1, col2) VALUES ('val1', 'val2')
    INTO SCHEME.TABLE (col1, col2) VALUES ('val3', 'val4')
SELECT 1 FROM DUAL;

edited Nov 29 '18 at 20:18

answered Nov 29 '18 at 19:48

GMB

216,147
25
84
135

if i have thousands of rows to insert, and i send them from python process, will multiple INSERT operation be slower than solution from duplicated question? – Ladenkov Vladislav Nov 29 '18 at 19:52
Just found out about the « INSERT ALL » statement in Oracle, and updated my answer accordingly – GMB Nov 29 '18 at 20:06

Inserting several rows in table

1 Answers1