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

Hi All,

Would someone be so kind as to remind me of the syntax required to copy data
from one table to another?

Kind Regards,
Keith

On 2/24/06 1:42 PM, "Keith Worthington" <keithw@narrowpathinc.com> wrote:

> Hi All,
>
> Would someone be so kind as to remind me of the syntax required to copy data
> from one table to another?

Hi, Keith.  Will:

Insert into table2 [column1, ...]
Select * from table1;

Do what you want?

Sean

If you want an exact copy (using another method), without indexes or relying objects, you shall try:

`SELECT * INTO table_b FROM table_a`

Check the manpages, SELECT, or in the psql shell: \h SELECT
--------------

Another option:

pg_dump the data as INSERT replacing the table name on the result with the new table name, then psql'it.

----------

Another option:

run a classic pg_dump, using COPY, and replace the table name with the new one.

-------

Consider in all cases, that the index maintenance will be a cost if you  already have the indexes created before running the populate. You may want to drop that indexes, populate and recreate indexes after.

Vacuum analyze should be the last command on this move, afaik.

Best wishes,
Guido




--
/"\   ASCII Ribbon Campaign  .
\ / - NO HTML/RTF in e-mail  .
X  - NO Word docs in e-mail .
/ \ -----------------------------------------------------------------

> On Fri, 24 Feb 2006 19:19:58 +0000, Guido Barosio wrote
> > On 2/24/06, Sean Davis <sdavis2@mail.nih.gov> wrote:
> > > On 2/24/06 1:42 PM, "Keith Worthington" <keithw@narrowpathinc.com> wrote:
> > >
> > > Hi All,
> > >
> > > Would someone be so kind as to remind me of the syntax
> > > required to copy data from one table to another?
> >
> > Hi, Keith.  Will:
> >
> > Insert into table2 [column1, ...]
> > Select * from table1;
> >
> > Do what you want?
> >
> > Sean
>
> If you want an exact copy (using another method), without indexes or relying
> objects, you shall try:
>
> `SELECT * INTO table_b FROM table_a`
>
> Check the manpages, SELECT, or in the psql shell: \h SELECT
> --------------
>
> Another option:
>
> pg_dump the data as INSERT replacing the table name on the result with
> the new table name, then psql'it.
>
> ----------
>
> Another option:
>
> run a classic pg_dump, using COPY, and replace the table name with the
> new one.
>
> -------
>
> Consider in all cases, that the index maintenance will be a cost if you
> already have the indexes created before running the populate. You may want
> to drop that indexes, populate and recreate indexes after.
>
> Vacuum analyze should be the last command on this move, afaik.
>
> Best wishes,
> Guido

Thank you both Sean and Guido.

One problem I was having was that I didn't want to use a SELECT INTO to create a
temporary table.  Then have to use the COPY command to write the data to a file
and finally a second COPY command to read the data into the target table.

The other challenge was that I couldn't just use the SELECT INTO command because
the target table already existed.

I ended up with an insert and a subselect.  I do not know if this is the best
way but it worked for me.

INSERT INTO tbl_target
   (
      SELECT column_a,
             column_b,
             column_c
        FROM tbl_source
       WHERE condition
   );

Kind Regards,
Keith