newbie sql question...

Jason Ziegler wrote:

I have a query that works in mysql:
"INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text stuff',
'text stuff' ), ( '2', 'more text', 'even more text')"

I'm not SQL expert bunt I don't think the INSERT statement will let you
insert more than one set of values at a time. You need to do one INSERT
for each set of values. Someone *will* correct me if I am wrong :)

You need:

INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text stuff',
'text stuff' );
INSERT INTO table1 ( id, column2, column3 ) VALUES ( '2', 'more text',
'even more text');

HTH,

Jean-Christian Imbeault

I'm a newbie too but I believe that you need to do:

INSERT INTO table1 ( id, column2, column3 )
values (1,'text stuff','text stuff');
INSERT INTO table1 ( id, column2, column3 )
values (2,'more text stuff','text stuff');

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "Vincent Hikida" <vhikida@inreach.com>
To: "Jason Ziegler" <moo@zigfam.org>
Sent: Monday, May 26, 2003 10:48 PM
Subject: Re: [GENERAL] newbie sql question...

On Tue, 27 May 2003, Jason Ziegler wrote:

Hello folks, I'm new to this list, and have been putting up with mysql
for too long, so please forgive me for asking such a newbie question as
this:

I have a query that works in mysql:
"INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text
stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"

When I try this in postgresql, it won't work:
"INSERT INTO table1 ( 'id', 'column2', 'column3' ) VALUES ( '1', 'text
stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"

There are two problems with this. The first is that postgresql doesn't
yet support multiple rows in the values section iirc, the second is that
single quoting the column names will give a parse error.

You can either use multiple inserts or possibly copy as workarounds.

Dear Vincent, Stephan & Jean-Christian,

Thank you for your replies!
I ended up doing exactly what you all advised which of course worked
just fine.

Jason

On Tuesday, May 27, 2003, at 10:41 AM, Stephan Szabo wrote:

On Tue, May 27, 2003 at 00:07:48 -0500,
Jason Ziegler <moo@zigfam.org> wrote:

Hello folks, I'm new to this list, and have been putting up with mysql
for too long, so please forgive me for asking such a newbie question as
this:

I have a query that works in mysql:
"INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text
stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"

It is possible to do more than one insert in a single statment. You
can build a select list using a union between the rows being added
and use the insert from a select to load the data. I suspect that
this won't work well for really long lists. I don't know that it is
any faster than using multiple inserts in a single transaction.

On Tue, 27 May 2003, Jason Ziegler wrote:

Hello folks, I'm new to this list, and have been putting up with mysql
for too long, so please forgive me for asking such a newbie question as
this:

I have a query that works in mysql:
"INSERT INTO table1 ( id, column2, column3 ) VALUES ( '1', 'text
stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"

When I try this in postgresql, it won't work:
"INSERT INTO table1 ( 'id', 'column2', 'column3' ) VALUES ( '1', 'text
stuff', 'text stuff' ), ( '2', 'more text', 'even more text')"

As others have pointed out, this won't work.

If you need to insert the two records as a single transaction, then do:

begin;
insert...1
insert...2
commit;

Also, there was some discussion on the hackers list a while back about
implementing this feature. don't know if it's in CVS tip or if anyone's
even working on it, but it's on someone's radar dish, just way down on
their list of things to do I think.

On Tue, May 27, 2003 at 11:59:01AM -0600, scott.marlowe wrote:

Also, there was some discussion on the hackers list a while back about
implementing this feature. don't know if it's in CVS tip or if anyone's
even working on it, but it's on someone's radar dish, just way down on
their list of things to do I think.

AFAIR someone actually implemented it, but in a way that was not
satisfactory to the hacker team, so it was not included.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"In Europe they call me Niklaus Wirth; in the US they call me Nickel's worth.
That's because in Europe they call me by name, and in the US by value!"