Bind parameter error occurs when using mysql queries stored in variables using perl?

I fear I rather confused you with one of my comments yesterday. Here's what I said:

Looks like this has nothing to do with your Perl. Your SQL is returning the wrong data (for reasons that I don't understand). Here's what to do. Open a mysql session on the source database. Write an SQL statement that returns the correct data. Add the insert into ... clause to the front of that SQL. Execute that SQL from Perl

I'll try to unpick the misunderstandings here. But, given how long we've been answering questions on this seemingly simple task, I do wonder if it time for you to give up and employ a programmer to do this for you.

This task has two phases. First you need to identify an SQL statement which selects the data that you interested in. Secondly, you need to convert that select statement into an insert into ... statement and execute that statement from your Perl program.

For the first stage, you don't need Perl at all. You just need a mysql command line program which is connected to your source database. You then try out a few SQL statements until you find one that gives you the data you need. It looks like you think the statement you need is:

select *
from   data_1m.wms
where  time = (select max(time) from wms)

That's great. I would probably make two tweaks to that SQL. I'd explicitly list the columns that I'm selecting and I'd specify the database that the wms table is in. So it would look like this:

select time, available, closed, used, busy, reserved, down
from   data_1m.wms
where  time = (select max(time) from data_1m.wms)

Now we need to convert that to an insert ... statement. That probably looks like this:

insert into data_current.wms(time,available,closed,used,busy,reserved,down)
select time, available, closed, used, busy, reserved, down
from   data_1m.wms
where  time = (select max(time) from data_1m.wms)

(That's slightly different to what you had - you don't need the values keyword in an insert ... select ... statement.)

So that's what you need to run from your Perl program. In fact you can test it from your mysql command line program first to ensure that it does the right thing.

Now we come to your current problem. And this is where I start to think that you might be better off just paying someone else to do this for you - because this problem is caused by you simply not reading (or not understanding) the basics of DBI.

Running a query in DBI is a two-stage process. You prepare() the query and that gives you back a statement handle. You then call execute() on that statement handle to actually execute the query. Nowhere in the documentation is it even hinted that you can pass another (new) statement to the execute() method on an existing statement handle. If you want to run another statement then you need to prepare() that statement to get a new statement handle and then run execute() on that statement handle.

(Passing parameters to execute() is allowed in order to define values that are inserted into "bind points" in your SQL. That's why you got the error that you did. But you're not using bind points.)

So to do what you wanted to do in your code, you would write code like this:

my $stm1="select * from data_1m.wms  where time =(select max(time) from wms)";
my $stmt2="insert into data_current.wms(time,available,closed,used,busy,reserved,down) values(select * from data_1m.wms  where time =(select max(time) from wms)";
my $sth1 = $DBH1->prepare( $stmt1 );
$sth1->execute() or print "Could not insert data";            
$sth1->finish;
my $sth2 = $DBH1->prepare( $stmt2 );
$sth2->execute;
$sth2->finish;
$DBH1->disconnect();

But (as I hope I've made clear above) you don't need to do this as $stmt1 is completely unnecessary. Also (as Chankey points out) it's far easier to run insert ... statements using the do() method than the prepare()/execute() cycle.

I apologise if my comments yesterday confused you. I really tried to be a clear as possible. But I really think you need to step back and take a couple of deep breaths before continuing with this problem. It is starting to look a bit like you are programming by changing things at random - and that is never a good approach.

The typical method call sequence for a non-SELECT statement is:

prepare,
  execute,
  execute,
  execute

Now in your case you are trying to insert data into table, which is non-SELECT statement. So you should follow the same approach.

1) Prepare the statement

my $sth = $dbh->prepare("INSERT INTO...");

2) Execute the prepared statement

$sth->execute(); #pass @bind_values if there are any

Alternatively you could also use do() method.

$rows_affected = $dbh->do("INSERT INTO ...");

Please go through the documentation of DBI for more details.

DBD::mysql::st execute failed: called with 1 bind variables when 0 are needed

You are getting the above error because you are passing another statement to execute method, whereas execute method accepts only bind values, which are none in your case.