Perl Inter-Database Transfer

Question

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

See this SO post: [How to copy data from one table to another new table in MySQL?](http://stackoverflow.com/questions/7482443/how-to-copy-data-from-one-table-to-another-new-table-in-mysql) — ThisSuitIsBlackNot, Apr 02 '14 at 18:03
Comments about your DBI usage: 1) You don't need two handles to the same database. Call `connect` once and reuse the handle in subsequent queries. 2) If you set the [`RaiseError`](https://metacpan.org/pod/DBI#RaiseError) option to `connect`, your program will automatically die on error, so you don't have to put `or die ...` after every database action: `my $dbh = DBI->connect($dsn, $user, $pass, { RaiseError => 1 });` — ThisSuitIsBlackNot, Apr 02 '14 at 18:08
And don't call `fetch_*` for non-`SELECT` statements. `execute` will return the number of rows affected for a non-`SELECT`. — ThisSuitIsBlackNot, Apr 02 '14 at 18:10

Miller · Answer 1 · 2014-04-02T18:14:34.897

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.

Your query is pretty inefficient since MySQL provides [INSERT ... SELECT](http://dev.mysql.com/doc/refman/5.5/en/insert-select.html). — ThisSuitIsBlackNot, Apr 02 '14 at 18:13
@ThisSuitIsBlackNot Yes it potentially is, but there were enough other potential issues that I wanted to address that efficiency was not my primary concern. User should understand the brute force method and its pitfalls before jumping to a shortcut. — Miller, Apr 02 '14 at 18:18

score 1 · Answer 2 · edited May 23 '17 at 12:28

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.

Perl Inter-Database Transfer

2 Answers2