in reply to I got confuse with INSERT

Here's (kind of) what I would do. Everything I do is explained in more detail in the DBI docs. Definitely read the section on placeholders. Not only does it handle quoting for you, it makes this easier by predefining an array with your column names.

I'm guessing the column names in the 1st DB are the same as the second DB. 

## UNTESTED
## Setup the column names - This way when you need to change it, you o
+nly change in one place
## The column names will also stay in order
my @col_names        = qw/reg pat ped adnasec impexp cveped adnaent cr
+upimp rfcimp curpaa/;
my $col_names_string = join(',',@col_names);

my $dbhX1 = DBI->connect('dbi:XBase(RaiseError=1):');
my $select1 = $dbhX1->prepare("SELECT $col_names_string FROM r501");
$select1->execute();

## Now we create the string with placholders. 
my $placeholders     = join(',',map { '?' } @col_names );
my $sql = "INSERT INTO r501 ( $col_names_string  ) VALUES ( $placehold
+ers )";

## We are going to call this quite a bit so lets prepare it out side o
+f the loop
my $sth = $mysql_dbh1->prepare( $sql );

while  ( my @row = $select1->fetchrow_array() ){
  ## Stick the result in the other database
  $sth->execute(@row);
}

[download]
grep
One dead unjugged rabbit fish later...

Replies are listed 'Best First'.
Re^2: I got confuse with INSERT
by padawan_linuxero (Scribe) on Mar 17, 2008 at 16:16 UTC
    Thank you for the example, but is there a faster way to do this? this is my other problem I need to read these tables in DBF to be able to provide to the user with real time information .
    But every time I run it it takes like 10 to 15 minutes in runnning
[reply]
      There is a not a (practical) programmatic way to speed up that code (without knowing more). But I think you are heading down to XY_problem Territory.

      Can you give a clearer spec of what you are really trying to accomplish?
      Because, I can almost guarantee that copying a set of data from one table to another is not the solution.

      grep
      One dead unjugged rabbit fish later...
[reply]
        yes is like this:
        We have a very obsolete program that runs on FoxPro for DOS
        but for a big reason ($$$$) we cant change it, our customers
        want to see the data send to us in a more easy way like a web page
        so I need to take the data from several DBF and turn them into MySQL tables to work with them the thing is that in the convertion is taking a lot of time, and the clients wants to see them in real time
        working with mysql is quite fast but when I need to do the part of bringing new data from dbf to mysql it take sometime like 7 minutes for one table (a small one) so imagine a big table with almost 3 times that size it takes forever.
        So I try to do it directly with DBF but it takes more time.
[reply]

In Section Seekers of Perl Wisdom