Dump mysql view as a table with data

Question

Say I have a VIEW on my database, and I want to send a file to someone to create that views output as a real TABLE on their database.

mysqldump of course only exports the 'create view...' statement (well ok it includes the create table, but no data)

What I have done is simply duplicate the view as a real table and dump that. But for a big table its slow and wasteful. (create table tmptable select * from myview)

Short of creating a script that mimics the behaviour of mysqldump and does this, is there a better way?

Thanks!

Answer 1

One option would be to do a query into a CSV file and import that. To select into a CSV file:

From http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Answer 2

OK, so based on your CSV failure comment, start with Paul's answer. Make the following change to it:

 - FIELDS TERMINATED BY ','
 + FIELDS TERMINATED BY ',' ESCAPED BY '\'

When you're done with that, on the import side you'll do a "load data infile" and use the same terminated / enclosed / escaped statements.

Answer 3

@Artem: Put the select statement into a file, and do

mysql -uroot -ppassword < file_containing_statement

or remotely

ssh root@mysql_host "mysql -uroot -ppassword < file_containing_statement"