Inserting multiple rows in a single Oracle SQL query:

Question

Inserting multiple rows in a single Oracle SQL query. Here is the following query I am trying to use. Can anyone tell me the error that I am having and how to go about it. Thanks.

INSERT ALL INTO 
     "SCOTT"."GREATCOLOR1" (
           COLOR, 
           PAUL, 
           JOHN, 
           TIM, 
           ERIC
     )VALUES (
           'White', 
           '1', 
           '5', 
           '1', 
           '3') 
     INTO "SCOTT"."GREATCOLOR1" (
           COLOR, 
           PAUL, 
           JOHN, 
           TIM, 
           ERIC
    )VALUES (
           'Yello', 
           '8', 
           '4', 
           '3', 
           '5') 
    INTO "SCOTT"."GREATCOLOR1" (
           COLOR, 
           PAUL, 
           JOHN, 
           TIM, 
           ERIC
    ) VALUES (
           'Black', 
           '2', 
           '2', 
           '9', 
           '1') 
    SELECT * FROM dual;

INSERT ALL INTO "SCOTT"."GREATCOLOR1" (COLOR, PAUL, JOHN, TIM, ERIC) VALUES ('White', '1', '5', '1', '3') INTO "SCOTT"."GREATCOLOR1" (COLOR, PAUL, JOHN, TIM, ERIC) VALUES ('Yello', '8', '4', '3', '5') INTO "SCOTT"."GREATCOLOR1" (COLOR, PAUL, JOHN, TIM, ERIC) VALUES ('Black', '2', '2', '9', '1') SELECT * FROM dual; — Gopu Alakrishna, May 26 '16 at 11:48
Possible duplicate of [Inserting multiple rows in a single SQL query?](http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query) — J. Chomel, May 26 '16 at 12:00
Hi @Gopu, have you read this crucial page: http://stackoverflow.com/help/how-to-ask ? especially the part about searching beforehand you should. — J. Chomel, May 26 '16 at 12:02
Your table design seems messed up. Don't have one column per person, store their values in separate rows instead!!! Like (White, Paul, 1) etc — jarlh, May 26 '16 at 12:06

score 1 · Accepted Answer · answered May 26 '16 at 11:59

You where close, but you have much to learn.

Here is how you could do it:

INSERT INTO "SCOTT"."GREATCOLOR1" (COLOR, PAUL, JOHN, TIM, ERIC)
          select 'White', '1', '5', '1', '3' from dual
union all select 'Yello', '8', '4', '3', '5' from dual
union all select 'Black', '2', '2', '9', '1' FROM dual
;

Inserting multiple rows in a single Oracle SQL query:

1 Answers1