SQL Insert Into - Multiple Rows - Oracle

Question

I have a query where I am trying to insert data into my database that I have created by using SQL commands such as

CREATE TABLE CUSTOMER
(
   REFERENCE NUMBER(5) PRIMARY KEY,
   COMPANY VARCHAR2(20),
   ADDRESS VARCHAR2(30),
   TELEPHONE NUMBER(12),
   CONTACT VARCHAR2(20)
);

I am trying to insert data using multiple rows,

I am pretty sure this syntax is right but it gives back a error of "ORA-00911: invalid character"

This is what I am inserting:

insert into customer values (130,'Affright Retail','18 Redingote Dell Tonbridge TN46 7JF',01208830667,'John Elrick');

insert into customer values (149,'Askew Inc.','37 Unmediated Parkway Walsall UB20 9WA',01299818072,'Sean Walker');

Yes, we have tried that. But we can manage to process one line at a time and it works fine but if we try multiple then it gives us this error... — user1028145
– user1028145, Commented Nov 3, 2011 at 16:46
Try this one: insert into customer values (130,'Affright Retail','18 Redingote Dell Tonbridge TN46 7JF',01208830667,'John Elrick'), values (149,'Askew Inc.','37 Unmediated Parkway Walsall UB20 9WA',01299818072,'Sean Walker'); — Aurelio De Rosa
– Aurelio De Rosa, Commented Nov 3, 2011 at 16:48
@AurelioDeRosa - You cannot specify multiple VALUES clauses in a single INSERT statement in Oracle. — Justin Cave
– Justin Cave, Commented Nov 3, 2011 at 16:49
Just tried it, this is the error we recieve "ORA-00933: SQL command not properly ended" - Thanks for trying — user1028145
– user1028145, Commented Nov 3, 2011 at 16:50
@JustinCave do you have a solution for us to insert multiple rows? — user1028145
– user1028145, Commented Nov 3, 2011 at 16:50

Justin Cave · Accepted Answer · 2011-11-03 16:48:47Z

1

What application are you using to run these commands?

If you are trying to run multiple statements from a custom application, you likely want to create a single PL/SQL block

BEGIN
  INSERT INTO customer( customer_id, 
                        customer_name, 
                        customer_address, 
                        customer_code, 
                        customer_contact )
    VALUES( 130,
            'Affright Retail',
            '18 Redingote Dell Tonbridge TN46 7JF',
            01208830667,
            'John Elrick');

  INSERT INTO customer( customer_id, 
                        customer_name, 
                        customer_address, 
                        customer_code, 
                        customer_contact )
    VALUES( 149,
            'Askew Inc.',
            '37 Unmediated Parkway Walsall UB20 9WA',
            01299818072,
            'Sean Walker' );
END;

If you are running these in SQL*Plus or some PL/SQL IDE, you may need to use a tool-specific separator (in SQL*Plus, that would be a / on a separate line).

answered Nov 3, 2011 at 16:48

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

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

2 Comments

user1028145 Over a year ago

We are creating it using Oracle 10g Express Edition, SQL Command.

user1028145 Over a year ago

Thanks we needed the "Begin" and "End" statements involved.

Ben · Accepted Answer · 2011-11-03 16:55:32Z

1

try each insert individually to make sure both are valid. If they both seem to work ok individually then try putting a slash("/") after each insert and that should take care of it. see below.

insert into customer values (130,'Affright Retail','18 Redingote Dell Tonbridge TN46 7JF',01208830667,'John Elrick');
/
insert into customer values (149,'Askew Inc.','37 Unmediated Parkway Walsall UB20 9WA',01299818072,'Sean Walker');
/

edited Nov 3, 2011 at 16:55

answered Nov 3, 2011 at 16:49

Ben

7843 gold badges12 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL Insert Into - Multiple Rows - Oracle

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