Re: 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