CODEWITHSUNDEEP
perldbi
user7078835
user7078835

Reputation:

How to insert table into another database using perl?

1.I have two databases(i.e database_1m and database_current)

2.I have inserted some columns in include_status table(i.e in database_1m).I want to insert the same tables in the database_current.

3.Each database has the time field in database_1m i will get all timings but in the second database i need only the recent time rows so i used mysql query to take the recent updated time rows(i.e select * from include_status where time=(select max(time) from include_status);)

4.Here i dont konw how to use both databases in the same subroutine using perl? code:

sub data
{

    $DBH = &connect or die "Cannot connect to the sql server \n";
    $DBH->do("USE database_1m;");
    $stmt = "INSERT INTO include_status(time,available,closed,used,busy,reserved,down) VALUES(\"$current_time\",\"$include{'D'}\",\"$include{'B'}\",\"$include{'A'}\",\"$include{'C'}\",\"$include{'R'}\",\"$include{'+'}\")";


            my $sth = $DBH->prepare( $stmt );
            $sth->execute() or print "Could not insert data";
    $sth->finish;
    $DBH->disconnect();




}

Mysql query:(to insert the recent updated rows)

 select * from include_status where time=(select max(time) from include_status);

Upvotes: 0

Views: 933

Answers (2)

Dave Cross
Dave Cross

Reputation: 69244

You want the "INSERT ... INTO ... SELECT ..." which allows you to run a select query and insert the results directly into another table. If the two tables are in different databases on the same server, then you should be able to do it directly in one statement. Something like:

INSERT
INTO database_current.insert_status
SELECT *
FROM database_1m.insert_status
WHERE [some where clause]

And I'll just reiterate some points that I made the last time this code was posted.

  1. Please don't call subroutines using &. It will confuse you at some point. Please use connect() instead.
  2. The fact that you don't declare $DBH with my makes me wonder if you have use strict in your code. You should always include use strict and use warnings.

Upvotes: 1

user2082599
user2082599

Reputation:

the insert will be something like this:

my $stmt = "INSERT 
            INTO destination_table(time,available,closed,used,busy,reserved,down)
            SELECT time,available,closed,used,busy,reserved,down from original_table";

Upvotes: 0

Related Questions