Use executed statement to insert results in another table (Perl DBI and MySQL)

Question

I have the need to execute a query in MySQL using Perl and DBI and insert the results in another table. The second table is created on the fly to define the same number of fields that the original query returned, plus one more field.

I have no way to know in advance the first query or how many fields it will return. So I'm doing this very ugly thing (summarized):

# Execute the received query
my $sth = $dbh->prepare($received_sql);
$sth->execute;

# Get the fields names and create the new table
my @fields = @{$sth->{NAME}};
my $create_table = "...some sql and a map from @fields...";

# Start getting the results from the query one by one just to insert them into the new table
while (my @record = $sth->fetchrow_array) {
    my $sql = "insert into `new_table` (";
    $sql .= join ', ', map { "`$_`" } @fields;
    $sql .= ") values (";
    $sql .= join ', ', map { '?' } @fields;
    $sql .= ')';
    my $insert = $dbh->prepare($sql)
    $insert->execute(@record);
}

It would be perfect if I could just do this:

insert into `new_table` (...) select (...) from ...

but, I need to know the names of the fields returned from the first query and create the corresponding table before inserting data into it.

So, I've been wondering if, since $sth is an actual prepared statement... is there anyway to use it to tell MySQL to insert the results of that statement into another table, without making it pass record by record to Perl?

Thanks in advance for your comments.

Francisco

golimar · Accepted Answer · 2013-04-30 14:37:03Z

4

You can use statements like these if you don't want to specify every single field:

CREATE TABLE newtable LIKE oldtable (and possibly using ALTER TABLE after this to add the new field)
INSERT INTO newtable SELECT * FROM oldtable
CREATE TABLE AS [$received_sql]

answered Apr 30, 2013 at 14:37

golimar

2,5881 gold badge24 silver badges37 bronze badges

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

2 Comments

Francisco Zarabozo Over a year ago

@eggyal: Wow, that sounds exactly like what I could use. I'll try it right now. Please add your answer. :-)

Francisco Zarabozo Over a year ago

golimar: Thanks for your answer. The reason it didn't work for me is that, as I mentioned, I can't know in advance the query that will be executed so I don't know how to create the new table until I have the result (number and type of fields). However, the comment from @eggyal was a perfect solution and I didn't know I could do that. Also, I don't know why he deleted his comment. I was just very lucky to see it on time.

Francisco Zarabozo · Accepted Answer · 2013-04-30 21:04:17Z

This answer was actually from @eggyal (left as a comment) but then he deleted the answer and never came back.

Anyway, I want to share his answer because it was extremelly useful. Maybe something too simple, but I just didn't have an idea I could just do that:

create table `new_table` select * from `other_table`

And I can even do something like:

create table `new_table` select SQL_CALC_FOUND_ROWS * from `other_table` limit 50;
select found_rows();

Which is simply great!

The speed is great and I don't even need to worry about the fields data type, MySQL does it automatically based on the passed query.

@eggyal: If you ever come back to this post, please feel free to add your answer and I'll select it. Thanks for your comment!

Francisco

Collectives™ on Stack Overflow

Use executed statement to insert results in another table (Perl DBI and MySQL)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related