A variation on Bodo's approach might use $sth->{NAME} or $sth->{NAME_lc} to get
a reference to an array of field names, instead of using $sth->{NUM_OF_FIELDS}.
The advantage of getting an array of fieldnames is that if you want, say only
148 fields out of 150, but the two you want to skip are in the middle, then you
can get the list of field names, manipulate the array to get rid of any fields
you don't want, then use the array to build your select and insert statements.
Both this approach and Bodo's are more portable, and of course are more likely
to survive any change in the number or sequence of field names in either table.
"Bodo Eing" <[EMAIL PROTECTED]> on 05/29/2001 06:09:11
AM
To: [EMAIL PROTECTED],
[EMAIL PROTECTED]
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: Re: Copy records from other database.
Date sent: Tue, 29 May 2001 12:23:47 +0700
From: "Krung Saengpole" <[EMAIL PROTECTED]>
Send reply to: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Copy records from other database.
> Hi all,
>
> 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.
I recommend the approaches posted by Anton and Steve. However, if
this does not work for some reason (and for the sake of maximum
portability), your task is not as difficult as it seems to be at
first glance (you fear typing 150 column names, don't you) and you
may try something like this (untested, add error checking yourself):
... connect and get your database handle $dbh, then
my $source_sth = $dbh->prepare("SELECT * FROM source_table WHERE
$where_clause");
$source_sth->execute();
my $number_of_placeholders = $source_sth->{NUM_OF_FIELDS} -1;
my $insert_sql = 'INSERT INTO target_table VALUES (' . '?, ' x
$number_of_placeholders . '?)';
my $target_sth = $dbh->prepare($insert_sql);
while (my @row = $source_sth->fetchrow_array) {
$target_sth->execute(@row);
}
... disconnect and go on with your program...
HTH
Bodo
