multiple INSERT

On Wed, 2 May 2001, Renaud Thonnart wrote:

Hello everyone!
I have readen in the doc of PostgreSQL that we can insert several tuples
in a table at one time.
But it is said that the tuples to insert must be the result of a SELECT.

Could someone explain me what is the advantage of that technique ?
Because if I have to build a temporary table before being able to
perform multiple INSERT, I work 2 times more, isn't it ?
Or perhaps does it exist a way to perform multiple insert without build
a temporary table; something like :
INSERT INTO table
VALUES(...,...,...),
VALUES(...,...,...);
I really need your advice!
Thank you all,
Sincerelly, Renaud THONNART

Let's suppose you have the tables:

CREATE TABLE source (
id int,
f1 text );1

INSERT INTO source VALUES (1, 'foo');
INSERT INTO source VALUES (2, 'bar');
INSERT INTO source VALUES (3, 'snog');

CREATE TABLE second (
id int,
f1 text );

You can't insert several rows simultaneously into second by using INSERT
... VALUES. That is, you CANNOT:

INSERT INTO second VALUES ( 1, 'foo' ), (2, 'bar');

Some databases support this, but PostgreSQL doesn't yet.

What the INSERT piece means is that you *can* do this:

INSERT INTO second SELECT * FROM source;

... and all three records will be copied into the table "second".

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Could someone explain me what is the advantage of that technique ?
Because if I have to build a temporary table before being able to
perform multiple INSERT, I work 2 times more, isn't it ?
Or perhaps does it exist a way to perform multiple insert without build
a temporary table; something like :
INSERT INTO table
VALUES(...,...,...),
VALUES(...,...,...);
I really need your advice!

SELECT * INTO is your friend.

Thank you all,
Sincerelly, Renaud THONNART

Diana Cionoiu