What is the SQL syntax to add multiple rows of data in one time?

Question

Here is my code and obviously there is something wrong with it.

INSERT INTO qdel(delno,delqty)
VALUES
(192,3422);
(1,2);
(203,20);

How to insert multiple rows of data in one statement?

You need to write three insert statements. And even if Oracle did support multi-row inserts, the rows need to be separated with a , not a ;. The semicolon ; terminates the complete statement — user330315
– user330315, Commented Nov 9, 2015 at 9:59

Anik Islam Abhi · Accepted Answer · 2015-11-09 09:00:23Z

2

You trying to insert mutiple row in same table.

But your process is not valid in oracle

Try like this

INSERT ALL
 INTO qdel(delno,delqty) VALUES (192,3422)
 INTO qdel(delno,delqty) VALUES (1,2)
 INTO qdel(delno,delqty) VALUES (203,20)
select * from dual

edited Nov 9, 2015 at 9:00

answered Nov 9, 2015 at 2:28

Anik Islam Abhi

25.4k8 gold badges61 silver badges82 bronze badges

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

2 Comments

MarioHo Over a year ago

Yes, that works fine. But I consider there may be some more convenient way.

user330315 Over a year ago

I have recently seen a rise in the usage of insert all - I never understood how that is more convenient than simply writing three insert statements because it doesn't remove the necessity to qualify the columns for each row.

Gordon Linoff · Accepted Answer · 2015-11-09 02:39:34Z

1

You can use insert . . . select:

INSERT INTO qdel(delno, delqty)
    select 192, 3422 from dual union all
    select 1, 2 from dual union all
    select 203, 20 from dual;

answered Nov 9, 2015 at 2:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

What is the SQL syntax to add multiple rows of data in one time?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related