Solved

MySQL: Create Dump From Select Query

Posted on 2011-09-16
6
401 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 78

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 78

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 25

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql db 3 83
Formating field inside mysql query 2 32
BIG DATA Question: Path to migrate current "Document Management" system 13 41
MySQL programmer starter 25 29
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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