Solved

MySQL: Create Dump From Select Query

Posted on 2011-09-16
6
390 Views
Last Modified: 2012-05-12
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
Comment
Question by:hankknight
6 Comments
 
LVL 12

Expert Comment

by:viralypatel
ID: 36550741
goto "view" menu and select view as text instead of view as grid.

You'll get the output as text...
0
 
LVL 16

Author Comment

by:hankknight
ID: 36550765
This is a MySQL question.  I use MySQL from a command line.  There is no menu view.
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 150 total points
ID: 36550795
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 16

Author Comment

by:hankknight
ID: 36550847
Can I do this without manually defining the columns, and can the output be SQL instead of CSV?
0
 
LVL 76

Expert Comment

by:arnold
ID: 36550958
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
 
LVL 24

Accepted Solution

by:
Tomas Helgi Johannsson earned 350 total points
ID: 36551721
                 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now