Inserting data into Oracle table (SQL)

Question

I already have a table built in oracle. Im trying to insert some data like this:

INSERT INTO movies_actor('name','id')  
VALUES  ('Nuno','2'),  ('Pedro','3'),  ('Jose','1');
select * from movies_actor;

I always get this error

ORA-00928: missing SELECT keyword

What am I doing wrong?

possible duplicate http://stackoverflow.com/questions/39576/best-way-to-do-multi-row-insert-in-oracle — Mincong Huang, Jun 10 '16 at 17:53

score 1 · Answer 1 · answered Jun 10 '16 at 17:39

1

I don't think you need the single quote around your field names.

answered Jun 10 '16 at 17:39

Brandon Parman

41
2

upvoted because Brandon does not enough enough reputation for comments. – Dan Bracuk Jun 10 '16 at 18:00

score 0 · Accepted Answer · answered Jun 10 '16 at 17:34

You need to do:

INSERT INTO TableName(Column1, Column2) 
VALUES('Nuno', '2');

In your example, it would be:

INSERT INTO movies_actor(name, id)  
VALUES ('Nuno','2');

INSERT INTO movies_actor(name, id)  
VALUES ('Pedro','3');

INSERT INTO movies_actor(name, id)  
VALUES ('Jose','1');

select * from movies_actor;

score 0 · Answer 3 · answered Jun 10 '16 at 17:40

0

Another way.

insert into table
(field1, field2)
select value1, value2
from dual
union
select value3, value4
from dual
etc

answered Jun 10 '16 at 17:40

Dan Bracuk

20,699
4
26
43

score 0 · Answer 4 · answered Jun 10 '16 at 17:53

You cannot insert multiple records in one statement using VALUES. You can either use Tenzin's solution or use INSERT ALL :

INSERT ALL
    INTO movies_actor(name, id) VALUES ('Nuno', '2')
    INTO movies_actor(name, id) VALUES ('Pedro', '3')
    INTO movies_actor(name, id) VALUES ('Jose', '1')
SELECT * FROM dual;

Inserting data into Oracle table (SQL)

4 Answers4