Re: Copy records from other database.

[Thomas A . Lowery]

Question:  Are you moving the data from/to the same rdbms?  (Oracle to
Oracle ...)

Here is an example.  This example does a straight copy from the
database tables.  This would only work if both tables contain
identically named columns.

#!/usr/local/bin/perl -w
# vim:ts=4:sw=4:ai:aw:nowrapscan
#
#

require 5.6.0;
use strict;
use warnings;

use DBI;

# Connect to the From database.
my $dbh_from = DBI->connect( 'dbi:mysql:database=names', '', '',
        { PrintError => 1, RaiseError => 1, AutoCommit => 1 } ) or
        croak $DBI::errstr;


# Connect to the To database.
my $dbh_to = DBI->connect( 'dbi:Oracle:yourdsnhere', 'user', 'password',
        { PrintError => 1, RaiseError => 1, AutoCommit => 1 } ) or
        croak $DBI::errstr;

my $your_table = shift || q{common_names};

# get a list of columns.
my $sel_sql = qq{select * from $your_table where 1 = 2};
my $sel_sth = $dbh_from->prepare( $sel_sql );
$sel_sth->execute;
my @sorted_columns = sort @{$sel_sth->{NAME_lc}};

$sel_sth->finish;

print join( "\n", "Columns: ", @sorted_columns, "" );

# create select statement pulling columns in sorted order.  I've
# included a limit here for testing.

my $sel_sql2 = "select " . join(",", @sorted_columns) . " from $your_table limit 100";

print "select statement:\n", $sel_sql2, "\n";

# create insert statement using columns in sorted order.
chop(my $bc = '?,' x @sorted_columns); 
my $ins_sql = "insert into $your_table(" . join(",", @sorted_columns)
. ") values (" . $bc . ")";

print "insert statemet:\n", $ins_sql,"\n";

# Now I've both the select and insert statement with the columns in
# the same order.

# Prepare the select using the "From" database handle.
my $sel = $dbh_from->prepare( $sel_sql2 );

$sel->execute;

# Prepare the insert using the "To" database handle
my $ins = $dbh_to->prepare( $ins_sql );

# Initialize counters:
my ($total_inserted, $total_selected);
$total_inserted = $total_selected = 0;

# Loop and fetch rows.
while( my @row = $sel->fetchrow_array ) {
        $total_selected++;
        $total_inserted += ( $ins->execute(@row)|| 0 );
}

exit;

END {

        print "Total inserted: $total_inserted\n";
        print "Total selected: $total_selected\n";

        $dbh_from->disconnect   if $dbh_from;
        $dbh_to->disconnect             if $dbh_to;
}

__END__

On Tue, May 29, 2001 at 12:23:47PM +0700, Krung   Saengpole wrote:

> I want to transfer record from one table in first database to another
> table in second database. Can I use DBI to do it w/o INSERT...
> statement? Because of many fields of my table (around 150 fields), or
> can use INSERT statement easily with some method?
> 
> Any helps would be gracefully thank.

-- 
Thomas A. Lowery
See DBI/FAQ http://tlowery.hypermart.net

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com