CODEWITHSUNDEEP
perl
Deano
Deano

Reputation: 12200

Perl Inter-Database Transfer

I'm new to Perl and I'm trying to select data from MySQL table StoreEvent and insert it into StoreEvent2 using DBI module. 

#!/usr/bin/perl

use DBI;
$dbh = DBI->connect('dbi:mysql:db_m2','root','rootboot')
or die "Connection Error: $DBI::errstr\n";
$sth = $dbh->prepare("select * from StoreEvent limit 10");
$sth->execute
or die "SQL Error: $DBI::errstr\n";
while (@row = $sth->fetchrow_array) {
print "@row\n";
}

and output as follows: 

# ./perl_data_dumper.pl
26152 2 1366735974109 1366735982127 9510 0
26153 2 1366735974614 1366735982639 9510 0
26154 2 1366735974613 1366735982637 9510 0
26155 2 1366735974614 1366735982639 9510 0
26156 2 1366735975621 1366735983642 9510 0
26157 2 1366735975621 1366735983643 9510 0
26158 2 1366735977133 1366735985160 9510 0
26159 2 1366735977134 1366735985164 9510 0
26160 2 1366735977637 1366735985659 9510 0
26161 2 1366735977639 1366735985673 9510 0

I'm not really sure how I can go about getting the results from first query and insert it into the other table.

here is what I have tried

use DBI;
$dbh = DBI->connect('dbi:mysql:db_m2','root','root')
or die "Connection Error: $DBI::errstr\n";
$sth = $dbh->prepare("select * from StoreEvent limit 10");
$sth->execute
or die "SQL Error: $DBI::errstr\n";
while (@row = $sth->fetchrow_array) {
print "@row\n";
}

$dbh2 = DBI->connect('dbi:mysql:db_m2','root','root')
or die "Connection Error: $DBI::errstr\n";
$sth2 = $dbh2->prepare("INSERT INTO StoreEvent2      (StoreID,StoreType,EventStart,EventEnd,AppserverID,Number") VALUES (?,?,?,?,?,?) );
$sth2->execute
or die "SQL Error: $DBI::errstr\n";
while (@row = $sth->fetchrow_array) {
print "@row\n";
}

Its not really working right, I wonder if someone can give me some insight.

Thank you

Upvotes: 0

Views: 115

Answers (2)

ThisSuitIsBlackNot
ThisSuitIsBlackNot

Reputation: 24063

Miller pointed out a number of issues with your code. I'd like to add that you can do this in a single query, using MySQL's INSERT ... SELECT syntax:

#!/usr/bin/perl

use strict;
use warnings;
use 5.010;

use DBI;

my $dbh = DBI->connect('DBI:mysql:db_m2', 'root', 'root', { RaiseError => 1 });

my $statement = <<'STATEMENT';
    INSERT INTO StoreEvent2 (StoreID, StoreType, EventStart, EventEnd, AppserverID, Number)
      SELECT StoreEvent.StoreID, StoreEvent.StoreType, StoreEvent.EventStart, StoreEvent.EventEnd, StoreEvent.AppserverID, StoreEvent.Number
      FROM StoreEvent
STATEMENT

my $rows = $dbh->do($statement);
say "Inserted $rows rows";

$dbh->disconnect;

I made the assumption that the column names are the same in both of your tables, but you can adjust the query if they aren't. Additional notes:

  • If you change the INSERT to INSERT IGNORE, rows that would cause duplicate-key violations are ignored.
  • The query above copies all rows in the first table. You can limit the rows to be copied by specifying a WHERE clause. I wouldn't use LIMIT by itself as you did in your original code, since that just grabs a random set of rows.
  • Setting RaiseError in connect causes an exception to be raised when there are errors, so you don't have to add or die ... after each database action.
  • If you will run this query multiple times in your program, use prepare and execute instead of do.

Upvotes: 1

Miller
Miller

Reputation: 35198

A few things

  • use strict; and use warnings;. use them, always.
  • If those tables are on the same database, just reuse the database handle.
  • You aren't actually attempting to insert anything in your first loop, and your second loop is just bogus.

Cleaned up:

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect('dbi:mysql:db_m2','root','root')
    or die "Connection Error: $DBI::errstr\n";

my $sth = $dbh->prepare("select * from StoreEvent limit 10");
$sth->execute or die "SQL Error: $DBI::errstr\n";

my $sth2 = $dbh2->prepare("INSERT INTO StoreEvent2 (StoreID,StoreType,EventStart,EventEnd,AppserverID,Number") VALUES (?,?,?,?,?,?) );

while (@row = $sth->fetchrow_array) {
    print "@row\n";
    $sth2->execute(@row) or die $dbh->errstr;
}

Note this code is still fragile for a couple reasons though:

  • You aren't specifying which columns you want from StoreEvent, but instead relying on *
    • There might be more columns than your attempting to INSERT into StoreEvent2, either now or at some later time if the table is altered.
    • The columns might be pulled in an order different than you're specifying in StoreEvent2
  • You're inserting based on a possible primary key (StoreID) without checking to see if the record already exists.

Upvotes: 3

Related Questions