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