Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Simple INSERT statement inSQL

Posted on 2009-07-10
6
Medium Priority
?
264 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
6 Comments
 
LVL 3

Accepted Solution

by:
Joep_Killaars earned 200 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

885 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