[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

ORACLE SQL DEVELOPER reports error

Posted on 2009-12-24
8
Medium Priority
?
252 Views
Last Modified: 2013-12-07
Can anyone help me find an error in this query please, I am trying to insert multiple rows at once but the blimming thing won't let me, saying the SQL command is not properly ended!! I am going mad here. Thanks for saving my sanity.

Please see the example in the code snippet.


INSERT INTO customer(
custid, firstname, lastname, houseno, street, city, postcode, telnum, email, county
) VALUES (
100012, 'Sergio', 'Fish', '34', 'Johny Walker Av', 'Kingston', 'K2 5OP', '02080987456', 'walkerJohn@bt.com', 'surrey' 
) , (
100013, 'Georg', 'Hanz', '12', 'Fishmongers Lane', 'Brighton', 'B2 0YU', '01940123456', 'hanzie@talktalk.co.uk', 'Kent');

Open in new window

0
Comment
Question by:czechmate1976
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 39

Expert Comment

by:thenelson
ID: 26119876
Try removing:
, (
100013, 'Georg', 'Hanz', '12', 'Fishmongers Lane', 'Brighton', 'B2 0YU', '01940123456', 'hanzie@talktalk.co.uk', 'Kent')

to change the query to:

INSERT INTO customer(custid, firstname, lastname, houseno, street, city, postcode, telnum, email, county) VALUES (100012, 'Sergio', 'Fish', '34', 'Johny Walker Av', 'Kingston', 'K2 5OP', '02080987456', 'walkerJohn@bt.com', 'surrey');

Does the query have carriage returns or did posting to EE add those?
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 600 total points
ID: 26120156
here it is ;)
/* Formatted on 12/24/2009 12:47:18 PM (QP5 v5.115.810.9015) */
INSERT INTO customer (custid,
                      firstname,
                      lastname,
                      houseno,
                      street,
                      city,
                      postcode,
                      telnum,
                      email,
                      county)
   SELECT   100012,
            'Sergio',
            'Fish',
            '34',
            'Johny Walker Av',
            'Kingston',
            'K2 5OP',
            '02080987456',
            'walkerJohn@bt.com',
            'surrey'
     FROM   DUAL
   UNION
   SELECT   100013,
            'Georg',
            'Hanz',
            '12',
            'Fishmongers Lane',
            'Brighton',
            'B2 0YU',
            '01940123456',
            'hanzie@talktalk.co.uk',
            'Kent'
     FROM   DUAL

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26120160
thenelson, removing ( does not solve the issue ;)
I guess he is trying to insert two records...
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 39

Expert Comment

by:thenelson
ID: 26120206
"I guess he is trying to insert two records..."
I figured that. It is not supported in Access or SQL server - wasn't sure about Oracle. Never thought about using a Union statement in an append query - Thanks!
0
 

Author Comment

by:czechmate1976
ID: 26120309
I am trying to insert multiple rows into a table using Oracle SQLdeveloper but it didn't work in any way.. I know that in mysql I could insert numerous records without these extra tags 'SELECT FROM DUAL UNION'.. but here it doesn't work the same..  
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26121421


INSERT all
INTO customer(custid, firstname, lastname, houseno, street, city, postcode, telnum, email, county)
VALUES (100012, 'Sergio', 'Fish', '34', 'Johny Walker Av', 'Kingston', 'K2 5OP', '02080987456', 'walkerJohn@bt.com', 'surrey')
INTO CUSTOMER(100013, 'Georg', 'Hanz', '12', 'Fishmongers Lane', 'Brighton', 'B2 0YU', '01940123456', 'hanzie@talktalk.co.uk', 'Kent')
SELECT 1 FROM DUAL;
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26121538
try this

insert into ...
(
select ... from dual
union
select ... from dual
)

or even this

insert into ...
select * from
(
select ... from dual
union
select ... from dual
) x

0
 

Author Closing Comment

by:czechmate1976
ID: 31669806
Thanks for your help!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question