Re: Moving rows to another database

On Fri, Jun 15, 2001 at 11:18:40AM +0100, Mark wrote:
> How would I go about moving or copying data between databases on the same
> server, without actually doing a dump ?  Using an SQL statement,
> something like:
>
> SELECT dbase_a.sometable.somefield
>   INTO dbase_b.sometable
>   FROM dbase_a.sometable

without a dump, eh? getting tricky, there. there's always perl--

    # note -- this untested pseudocode may install windows 2000
    # if you're not careful
    $dbh_from = DBI->connect(...);
    my $get_sth = $dbh_from->prepare("select * from...");
    $get_sth->execute();

    $dbh_to = DBI->connect(...);
    my $put_sth = $dbh_to->prepare("insert into ... values(?,?...)");

    while ($got = $get_sth->fetchrow_hashref()) {
        @val = map {...} keys %$got;
        $put_sth->execute( @val );
    }

    $get_sth->finish();
    $put_sth->finish();
    $dbh_to->disconnect();
    $dbh_from->disconnect();

...i think.

or you can settle for using pg_dump via pipe, as in

    pg_dump -a -t mytable from_database | psql to_database

...i think.

but using only sql? i hear inter-database connectivity is
something postgresql won't have for quite some time yet.

...i think.

--
I figure: if a man's gonna gamble, may as well do it
without plowing.   -- Bama Dillert, "Some Came Running"

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!