Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT INTO but append not create new table - How?

SELECT INTO but append not create new table - How?

From
"Alan T. Miller"
Date:
I have two tables. The first will contain recent records that are used on a
daily basis. The other table is identical but only exists to archive old
entries from the first table. I would like to know if there is a way in
postgreSQL to select all the old records from the first table and
automatically drop them into the second table.

I messed around with the 'INSERT INTO' SQL command, but this assumes that
the table you are 'selecting into' does not exist and you want to create
that table. My problem is that the table exists and I just want to append
the information to the archive table.

Is there a simple way to do this in SQL with PostgreSQL or am I going to
have to bring my result set out into a scripting language manipulate it
there and them do a series of INSERTS to update the archive table? This
option seems slow but perhaps the only option open to me at this point.

Any sugestions?

Alan

Re: SELECT INTO but append not create new table - How?

From
Tom Lane
Date:
"Alan T. Miller" <amiller@hollywood101.com> writes:
> I messed around with the 'INSERT INTO' SQL command, but this assumes that
> the table you are 'selecting into' does not exist and you want to create
> that table. My problem is that the table exists and I just want to append
> the information to the archive table.

I think you are looking for INSERT ... SELECT.  See the INSERT man page
or any book about SQL.

            regards, tom lane

Re: SELECT INTO but append not create new table - How?

From
"Alan T. Miller"
Date:
Thanks, just what I needed :)

Alan


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Alan T. Miller" <amiller@hollywood101.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Tuesday, January 27, 2004 7:28 PM
Subject: Re: [NOVICE] SELECT INTO but append not create new table - How?


> "Alan T. Miller" <amiller@hollywood101.com> writes:
> > I messed around with the 'INSERT INTO' SQL command, but this assumes
that
> > the table you are 'selecting into' does not exist and you want to create
> > that table. My problem is that the table exists and I just want to
append
> > the information to the archive table.
>
> I think you are looking for INSERT ... SELECT.  See the INSERT man page
> or any book about SQL.
>
> regards, tom lane
>