If your goal is just to make an exact copy of a table to a table with a
different name, you may not need DBI for that. I just did this a few
weeks ago in Oracle - this is from memory and may not be quite right,
but you'll hopefully get the idea:
create table table_b as (select * from table_a)
I did that command in sqlplus - table_b is an exact copy of table_a.
HTH.
Hardy Merrill
>>> Mark Martin <[EMAIL PROTECTED]> 03/11/04 04:21AM >>>
Hi,
bin using DBI for a while for data warehousing type stuff and up till
now I've had to change data on the fly as it moves from one database to
another :
my $sql01 = qq{SELECT FIELD01 .....FIELDn FROM PRODUCTION_TABLE};
my $sql02 = qq{INSERT INTO DATAWAREHOUSE VALUES (?,?,?............);
my $sth = $dbh->prepare($sql01);
$sth->execute;
while (my @row = $sth->fetchrow) { $data1 = $row[0]; $data2 =
$row[1]; .........manipulate the dat with perl ..... and so on........
But, now, I just want to copy one table into an exact replica and to
save on coding I want to avoid having to reference each DB column and
the contents of it per row. Here's what I've got so far, the SELECT is
working and I know pushing onto seperate arrays is wrong (gives
HASH(0x820c324) etc. with print print "@rows";) but I'm stuck on how to
take it further.
#!/usr/bin/perl
use DBI;use DBD::Oracle;
$dbh01 = DBI->connect("dbi:Oracle:PROD_DB", "user", "pwrd");
$dbh02 = DBI->connect("dbi:Oracle:DATA__WAREHOUSE", "user", "pwrd");
my $sth01 = $dbh->prepare(<<SQL);
SELECT * FROM PROD_DB
SQL
$sth01->execute();
while( my $row = $sth01->fetchrow_hashref) {
foreach my $col (keys %{$row} ) {
print "$col = $row->{$col}\n";
push(@cols,$col);
push(@rows,$row);
}
}
my $fields = join(', ', @cols);
my $values = join(', ', @rows);
$sql02 = qq{"INSERT INTO DATA__WAREHOUSE($cols) VALUES ($rows)"};
$sth02 = $dbh02->prepare($sql02) or die "Can't prepare SQL statement:
$DBI::errstr\n";
$sth02->execute();
