Обсуждение: Copy data from table to table

Hi All,

I can not think of a way to copy data directly betweeen two tables.

I can only think of two ways to copy the data indirectly.

Method 1:  Write a function that loops through the results of a select query
running individual inserts for each record.

Method 2:  Use SELECT INTO to put the desired data in a temporoary table; then
use COPY to store the data in a file and finally use COPY to read the data
from the file to the target table.

I have implemented the first method for situations where the source data ends
up in multiple tables but I want to check with all of you prior to creating a
function to do this for a single target table.

As a curious point of information I worked with a database once that allowed
you to SELECT INTO an existing table.  It was handy.

Kind Regards,
Keith

On Wed, Apr 27, 2005 at 09:16:09AM -0400, Keith Worthington wrote:
>
> I can not think of a way to copy data directly betweeen two tables.

Have you tried INSERT ... SELECT?

INSERT INTO foo (a, b, c) SELECT a, b, c FROM bar;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hi Keith,

Why not:

    INSERT INTO tablea SELECT * FROM tableb;

Or am I missing something about what you're trying to achieve?

Regards,
George


----- Original Message -----
From: "Keith Worthington" <keithw@narrowpathinc.com>
To: "PostgreSQL Novice" <pgsql-novice@postgresql.org>
Sent: Wednesday, April 27, 2005 8:16 AM
Subject: [NOVICE] Copy data from table to table


> Hi All,
>
> I can not think of a way to copy data directly betweeen two tables.
>
> I can only think of two ways to copy the data indirectly.
>
> Method 1:  Write a function that loops through the results of a select
> query
> running individual inserts for each record.
>
> Method 2:  Use SELECT INTO to put the desired data in a temporoary table;
> then
> use COPY to store the data in a file and finally use COPY to read the data
> from the file to the target table.
>
> I have implemented the first method for situations where the source data
> ends
> up in multiple tables but I want to check with all of you prior to
> creating a
> function to do this for a single target table.
>
> As a curious point of information I worked with a database once that
> allowed
> you to SELECT INTO an existing table.  It was handy.
>
> Kind Regards,
> Keith
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>

At 09:16 AM 4/27/05, Keith Worthington wrote:

>Hi All,
>
>I can not think of a way to copy data directly betweeen two tables.
>
>I can only think of two ways to copy the data indirectly.
>
>Method 1:  Write a function that loops through the results of a select query
>running individual inserts for each record.
>
>Method 2:  Use SELECT INTO to put the desired data in a temporoary table; then
>use COPY to store the data in a file and finally use COPY to read the data
>from the file to the target table.


INSERT INTO ...  (SELECT FROM ...)

http://www.postgresql.org/docs/8.0/interactive/sql-insert.html

         What is the last word in synopsis?

Frank Bax wrote:
> At 09:16 AM 4/27/05, Keith Worthington wrote:
>
>> Hi All,
>>
>> I can not think of a way to copy data directly betweeen two tables.
>>
>> I can only think of two ways to copy the data indirectly.
>>
>> Method 1:  Write a function that loops through the results of a select
>> query
>> running individual inserts for each record.
>>
>> Method 2:  Use SELECT INTO to put the desired data in a temporoary
>> table; then
>> use COPY to store the data in a file and finally use COPY to read the
>> data
>> from the file to the target table.
>
>
>
> INSERT INTO ...  (SELECT FROM ...)
>
> http://www.postgresql.org/docs/8.0/interactive/sql-insert.html
>

Thank you all very much.  I had not run across this command yet.  I have
run a few queries using it and it is going to work quite nicely for me.

--
Kind Regards,
Keith