Solved

Simple INSERT statement inSQL

Posted on 2009-07-10
6
254 Views
Last Modified: 2012-05-07
I'm trying to use the INSERT statement to insert multiple rows into a already created table using this code but it generates a syntax error. Incorrect syntax near ','.

I thought i would be able to insert multiple rows without having to specify the 'INSERT INTO' statement for every single row.  

I've checked already that the values are in the exact order of the column names.


Is this possible?

Code:

INSERT INTO easy_drinks

VALUES

('Blackthorn', 'tonic water', 1.5, 'pineapple juice', 1, 'stir with ice, strain into
cocktail glass with lemon twist'),
('Blue Moon', 'soda', 1.5, 'blueberry juice', 0.75, 'stir with ice, strain
into cocktail glass with lemon twist'),
('Oh My Gosh', 'peach nectar', 1, 'pineapple juice', 1, 'stir with ice, strain
into shot glass'),
('Lime Fizz', 'Sprite', 1.5, 'lime juice', .75, 'stir with ice, strain
into cocktail glass'),
('Kiss on the Lips', 'Cherry Juice', 2, 'apricot nectar', 7, 'serve over ice with straw'),
('Hot Gold', 'Peach nectar', 3, 'orange juice', 6, 'Pour hot orange juice in mug
and add peach nectar'),
('Lone Tree', 'soda', 1.5, 'cherry juice', .75, 'stir with ice, strain
into cocktail glass'),
('Greyhound', 'soda', 1.5, 'grapefruit juice', 5, 'serve over ice, stir well'),
('Indian Summer', 'apple juice' 2, 'hot tea', 6, 'add juice to mug and top off with hot tea'),
('Bull Frog', 'iced tea', 1.5, 'lemonade', 5, 'serve over ice with lime slice'),
('Soda and It', 'soda', 2, 'grape juice', 1, 'shake in cocktail glass, no ice');
0
Comment
Question by:SJ_Berwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 3

Accepted Solution

by:
Joep_Killaars earned 50 total points
ID: 24821532
You can not do this the way you want. There are some solutions.

if the values are in another table you could simply do an insert with a select clause.
You could also write a stored procedure to do this.

Why do you not want to write the insert line before each value list ?
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 24821602
mr. joep is right
if want to insert the data than do like this

insert into yourdata (
with yourdata as (
select 'Blackthorn' a1, 'tonic water' a2, 1.5 a3, 'pineapple juice' a4, 1 a5, 'stir with ice, strain into cocktail glass with lemon twist' a6 from dual union all
select 'Blue Moon', 'soda', 1.5, 'blueberry juice', 0.75, 'stir with ice, strain into cocktail glass with lemon twist' from dual union all
select 'Oh My Gosh', 'peach nectar', 1, 'pineapple juice', 1, 'stir with ice, straininto shot glass' from dual )
 select * from yourdata )
0
 
LVL 8

Expert Comment

by:shanikawm
ID: 24821659
See the record 'Indian Summer'. You have miss the comma after the  'apple juice'

should be

('Indian Summer', 'apple juice', 2, 'hot tea', 6, 'add juice to mug and top off with hot tea'),
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:shru_0409
ID: 24821689
i knew sir..... bu mr.author will do the same for 50 point
0
 

Author Closing Comment

by:SJ_Berwin
ID: 31601993
I thought I could save valuable time by having to omit the insert line each time, but thanks to all. !
0
 

Author Comment

by:SJ_Berwin
ID: 24821747
Thanks everyone, Points given to the first replier !
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

726 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