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
– Jeff Holt, Commented Jul 15, 2017 at 20:19

Gordon Linoff · Accepted Answer · 2017-07-15 18:52:26Z

0

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.

answered Jul 15, 2017 at 18:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mehmet sahin · Accepted Answer · 2017-07-15 18:33:52Z

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, 2017 at 18:33

mehmet sahin

8128 silver badges24 bronze badges

1 Comment

axiac Over a year ago

This is MySQL syntax, the question is about Oracle that doesn't support inserting multiple rows this way.

Collectives™ on Stack Overflow

SQL Multiple Insert into multiple rows

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related