SQL Multiple Insert into multiple rows

Question

I'm trying to insert multiple rows into a table without specifying the column names, however, I get an error on the first comma that its a partially recognized rule and it's giving me an error

INSERT INTO MY_EMPLOYEE
VALUES(126,'Popov', 'Olga', 'opopov', 8500), 
    (127, 'Chen', 'Ling', 'lcheng', 14500), 
    (128, 'Dunn', 'David', 'ddunn', NULL);

Is this something that you need to do only once of are you writing an application that will be put into the hands of lots of people? — Jeff Holt, Jul 15 '17 at 20:19

score 0 · Answer 1 · answered Jul 15 '17 at 18:33

0

Try this;

INSERT INTO MY_EMPLOYEE(col1_name,col2_name,col3_name,col4_name,col5_name)
VALUES(126,'Popov', 'Olga', 'opopov', 8500), 
    (127, 'Chen', 'Ling', 'lcheng', 14500), 
    (128, 'Dunn', 'David', 'ddunn', NULL);

answered Jul 15 '17 at 18:33

mehmet sahin

802
7
21

This is MySQL syntax, the question is about Oracle that doesn't support inserting multiple rows this way. – axiac Jul 15 '17 at 19:01

score 0 · Accepted Answer · answered Jul 15 '17 at 18:52

I don't think that Oracle supports VALUES with multiple records. Here is a simple alternative:

INSERT INTO MY_EMPLOYEE
    SELECT 126,'Popov', 'Olga', 'opopov', 8500 FROM DUAL UNION ALL
    SELECT 127, 'Chen', 'Ling', 'lcheng', 14500 FROM DUAL UNION ALL 
    SELECT 128, 'Dunn', 'David', 'ddunn', NULL FROM DUAL;

Note: I do highly recommend that you specify the columns for the insert, but that is another issue.

SQL Multiple Insert into multiple rows

2 Answers2

Linked

Related