How can I insert multiple row into oracle database using one insert statement?

sql oracle10g sql-insert
Moataz Aahmed Mohammed picture Moataz Aahmed Mohammed · Jan 21, 2013 · Viewed 78k times · Source

Possible Duplicate:
Best way to do multi-row insert in Oracle?

I have this insert statement

INSERT INTO mytable VALUES 
('val1', 'val2'),
('aa',   'cc'),
('ww',   'dd');

and I got this error ORA-00933: SQL command not properly ended

Answer

John Woo picture John Woo · Jan 21, 2013

you are using oracle, try this

INSERT ALL 
    INTO mytable ("id", "name")
         VALUES ('val1', 'val2')
    INTO mytable ("id", "name")
         VALUES ('aa', 'cc')
    INTO mytable ("id", "name")
         VALUES  ('ww', 'dd')
SELECT * FROM dual  ;

or simple

INSERT INTO mytable ("id", "name") VALUES ('val1', 'val2');
INSERT INTO mytable ("id", "name") VALUES ('aa', 'cc');
INSERT INTO mytable ("id", "name") VALUES ('ww', 'dd');

Related questions

"ORA-01733: virtual column not allowed here" when inserting into a view

I created a view called "view_employee" like this: CREATE VIEW view_employee AS SELECT employee.surname || ', ' || employee.name AS comp_name, employee.sex, sections.name AS section_name, employee_age FROM sections, employee WHERE employee.section = sections.…

Read More
CASE .. WHEN expression in Oracle SQL

I have the table with 1 column and has following data Status a1 i t a2 a3 I want to display the following result in my select query Status| STATUSTEXT a1 | Active i | Inactive t | Terminated a2 | Active a3 | Active One …

Read More
How to insert date values into table

How can I insert into table with different input using / ,with date datatype? insert into run(id,name,dob)values(&id,'&name',[what should I write here?]); I'm using oracle 10g.

Read More