how do i add or insert multiple rows in two columns, at the same time?

Question

how do i add or insert multiple rows in two columns, at the same time? i am trying this

insert into Gwish_bal(Account_code,Flag) values (
('10192885','I'),
('10146883','I')
);

and it is not working i keep getting ORA-00907: missing right parenthesis. i want to insert a thousand numbers and flag them I. the output i need is a table which looks like this

a

ccount code    flag
800000008        I
800000009        I
....
800100000        I

hahah sorry mr strawberry, i just am in a tight spot i hope you under stand — J.Mike1914, Jun 27 '17 at 07:18
Just so you know, sticking 'urgent' in your post sends it to the bottom of the pile. — Strawberry, Jun 27 '17 at 08:26

score 2 · Answer 1 · answered Jun 27 '17 at 07:09

2

This should work:

INSERT ALL
INTO Gwish_bal (Account_code, Flag) VALUES ('10192885', 'l')
INTO Gwish_bal (Account_code, Flag) VALUES ('10146883','I');

But the more common solution I have come across is:

INSERT INTO Gwish_bal(Account_code,Flag)
SELECT '10192885', 'l' FROM dual
  UNION ALL
SELECT '10146883','I' FROM dual;

answered Jun 27 '17 at 07:09

fhossfel

2,041
16
24

INSERT INTO Gwish_bal(Account_code,Flag) SELECT '10192885', 'l' FROM dual UNION ALL SELECT '10146883','I' FROM dual; – J.Mike1914 Jun 27 '17 at 07:14
INSERT INTO Gwish_bal(Account_code,Flag) SELECT '10192885', 'l' FROM dual UNION ALL SELECT '10146883','I' FROM dual; This one works, thank you but for like a thousand rows eg '100000','I' TO '109000', 'I'. how do i do that – J.Mike1914 Jun 27 '17 at 07:17
You can try with a PL/SQL table and FORALL. If you are outside the database, use a statement with bind variables. If you just want the data in the database use sqlldr. – fhossfel Jun 27 '17 at 07:39

score 1 · Answer 2 · answered Jun 27 '17 at 06:59

1

try

INSERT INTO Gwish_bal (Account_code, Flag)
VALUES
    ('10192885', 'l'),
    ('10146883', 'l');

answered Jun 27 '17 at 06:59

Idriss Benbassou

1,481
2
14
23

This isn't valid Oracle syntax. – Alex Poole Jun 27 '17 at 07:01
what is the right oracle syntax – J.Mike1914 Jun 27 '17 at 07:07

I3rutt · Answer 3 · 2017-06-27T08:04:52.877

1

Use this syntax:

INSERT ALL
  INTO Gwish_bal(Account_code,Flag) VALUES ('10192885','I')
  INTO Gwish_bal(Account_code,Flag) VALUES ('10146883','I')
SELECT * FROM dual;

for thousand rows:

insert INTO Gwish_bal(Account_code,Flag)
select to_char(level), 'I'
from dual
where level >= 10000
connect by level <= 11000

edited Jun 27 '17 at 08:04

answered Jun 27 '17 at 07:03

I3rutt

574
4
18

thank you!! i am new to this. so lets say i want add a thousand rows of account code and flag how would i go about it – J.Mike1914 Jun 27 '17 at 07:23
insert into Gwish_bal(Account_code, Flag) select Account_code, Flag from table_with_thousand_rows – I3rutt Jun 27 '17 at 07:27

Idriss Benbassou · Answer 4 · 2017-06-27T07:25:25.297

1

this should be work in oracle

INSERT ALL 
    INTO Gwish_bal ("Account_code", "Flag")
         VALUES ('10192885', 'l')
    INTO Gwish_bal ("Account_code", "Flag")
         VALUES ('10192885', 'l')
    INTO Gwish_bal ("Account_code", "Flag")
         VALUES  ('10192885', 'l')
 SELECT * FROM dual  ;

edited Jun 27 '17 at 07:25

answered Jun 27 '17 at 07:03

Idriss Benbassou

1,481
2
14
23

it didnt work, thank you anyways – J.Mike1914 Jun 27 '17 at 07:24
you welcome, i forgot the SELECT * FROM dual at the end ^^ – Idriss Benbassou Jun 27 '17 at 07:26

score 0 · Answer 5 · answered Jun 27 '17 at 06:59

0

You don't need to wrap the list.

insert into Gwish_bal(Account_code,Flag) values ('10192885','I'), ('10146883','I');

answered Jun 27 '17 at 06:59

EvgenyKolyakov

3,310
2
21
31

This isn't valid Oracle syntax. – Alex Poole Jun 27 '17 at 07:01
What version are you on ?? – EvgenyKolyakov Jun 27 '17 at 07:02
thank you how do i right the proper syntax or unwrap the list – J.Mike1914 Jun 27 '17 at 07:04

how do i add or insert multiple rows in two columns, at the same time?

5 Answers5