• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

MySQL: Create Dump From Select Query

I want to create a SQL dump that I can use to manually import the selected records to another database.

How can this query be modified to create SQL text that could be used for the dump?
select * from elqBooks WHERE `isbn` like '9780813343310'

Open in new window

0
hankknight
Asked:
hankknight
2 Solutions
 
viralypatelCommented:
goto "view" menu and select view as text instead of view as grid.

You'll get the output as text...
0
 
hankknightAuthor Commented:
This is a MySQL question.  I use MySQL from a command line.  There is no menu view.
0
 
arnoldCommented:
http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

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

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
hankknightAuthor Commented:
Can I do this without manually defining the columns, and can the output be SQL instead of CSV?
0
 
arnoldCommented:
Can you explain what you mean by SQL?

select * from elqBooks WHERE `isbn` like '9780813343310'
INTO OUTFILE '/tmp/elqbooks.txt'

This will likely be a tab separated data (tsv) data format.
Do you need it to have | as separator (fields terminated by '|')


0
 
Tomas Helgi JohannssonCommented:
                 Hi!

If you want to extract particular data from a table into a file which has the data surrounded by INSERT statement then you
use the mysqldump program with the where option
See the manual :)
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_where

So this command for you would be something like this
mysqldump -t -u [username] -p test elqBooks --where="isbn` like '9780813343310'"
see more examples here
http://www.electrictoolbox.com/mysqldump-selectively-dump-data/

Hope this helps.
Regards,
    Tomas Helgi
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now