How to insert table into another database using perl?

Question

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);

I am little confused. Do you want to insert tables into a different database, or insert rows into a different table on the same database? I ask because you mention " i want to insert the same tables from database_1m into database_current" but the code shows INSERT INTO include_status which tells me include_status is a table name? — user2082599
– user2082599, Commented Oct 27, 2016 at 7:00
I want to insert same table into another database (i.e databse_Current).But the second database should contain only the recent updated time rows .@user2082599 — user7078835
– user7078835, Commented Oct 27, 2016 at 7:07
the table then needs to be created if it does not exist and then you insert into table by selecting from the other table. The DBH connect is the exact same but you just add dbh2 as the connection name as dbh is already defined. — user2082599
– user2082599, Commented Oct 27, 2016 at 7:13
But how can i insert mysql query inside $stmt variable @user2082599 — user7078835
– user7078835, Commented Oct 27, 2016 at 7:17

Community · Accepted Answer · 2017-05-23 11:53:35Z

1

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.

Please don't call subroutines using &. It will confuse you at some point. Please use connect() instead.
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.

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Oct 27, 2016 at 8:08

Dave Cross

69.5k3 gold badges55 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2082599 · Accepted Answer · 2016-10-27 07:53:21Z

0

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";

answered Oct 27, 2016 at 7:53

user2082599

Collectives™ on Stack Overflow

How to insert table into another database using perl?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related