Help Copying records from one table to another.

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to copy all records from one table and then add them to another table. Here is the code as it stands:

$dbh = DBI->connect('dbi:ODBC:ReportDB') or die "Error opening DB: $DB
+I::errstr\n";

$sth = $dbh->prepare("Select * FROM dbo_Bulletins Without Matching Kno
+wnBulletins");

$sth->execute()  or die "Couldn't execute query: $DBI::errstr\n";

$sth->finish();


my @results = ();

while (@results = $sth->fetchrow_array)
{    
    (

     $sth = $dbh->prepare("insert into knownbulletins(BulletinID,CVENu
+mber,Title,DatePosted,DateRevised,Supported,Summary,Issue,ImpactSever
+ityID,PreReqSeverityID,MitigationSeverityID,PopularityServerityID)
     values('$results[0]','$results[1]', '$results[2]','$results[3]','
+$results[4]','$results[5]','$results[6]','$results[7]','$results[8]',
+$results[9],$results[10],$results[11],$results[12]);
     
     $sth->execute()  or die "Couldn't execute query: $DBI::errstr\n";

     $sth->finish();
[download]

Any help is appreciated. Thanks Steve

Comment on Help Copying records from one table to another. Download Code

Replies are listed 'Best First'.
Re: Help Copying records from one table to another. by dws (Chancellor) on Jun 12, 2002 at 21:06 UTC
Your immediate problem is the sequence `$sth->execute() $sth->finish() $sth->fetchrow_array()` [download] followed by reusing `$sth` to hold a different statement, before you're done fetching results from the first. This is masking a second problem, which is that if any of the data you're retrieving needs to be quoted, the SQL you're preparing inside of the loop is going to be bogus. Use two statements, using bind variables for the INSERT. Prepare both outside of the loop.	[reply] [d/l] [select]
Re: Help Copying records from one table to another. by vladb (Vicar) on Jun 12, 2002 at 21:09 UTC
I think if I had to do a similar thing, I'd rather use a module off the CPAN instead of re-inventing the wheel. There's actrually a pretty good Perl module that allows you to copy database content between multiple sources. Take a look at the DBIx::Copy module. `use DBIx::Copy; use DBI; my $dbh = DBI->connect('dbi:ODBC:ReportDB') or die "Error opening DB: +$DBI::errstr\n"; my %options=(); my $copy_handler= DBIx::Copy->new($dbh, $dbh, \%options); $copy_handler->copy (['dbo_Bulletins', 'knownbulletins']);` [download] Althought, I haven't had the chance to test this code, give the module a good try. I'm hopeful it should help. _____________________ `# Under Construction` [download]	[reply] [d/l] [select]
Re: Help Copying records from one table to another. by Abigail-II (Bishop) on Jun 13, 2002 at 10:19 UTC
Wouldn't you rather do a `select BulletinID, CVENumber, Title, DatePosted, DateRevised, Supported, Summary, Issue, ImpactSeverityID, PreReqSeverityID, MitigationSeverityID, PopularityID from dbo_Bulletins into knownbulletins` [download] (assuming the column names are the same - otherwise, just substitute the proper column names). This should be much faster than passing everything through a Perl program. Abigail	[reply] [d/l]
(wil) Re: Help Copying records from one table to another. by wil (Priest) on Jun 13, 2002 at 10:43 UTC
Is there a reason why you're trying to do this through Perl? I'd recommend doing this straight into your MySQL monitor with something like: `NSERT INTO database.new_table (SELECT * FROM database.old_table);` [download] This will work as long as both table structures are exactly identical. - wil	[reply] [d/l]