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(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!