MySQL: Create Dump From Select Query

Posted on 2011-09-16
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

Question by:hankknight
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
LVL 12

Expert Comment

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

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

Author Comment

ID: 36550765
This is a MySQL question.  I use MySQL from a command line.  There is no menu view.
LVL 78

Assisted Solution

arnold earned 150 total points
ID: 36550795

Excerpt from the link
SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'

Open in new window

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

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

Expert Comment

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 '|')

LVL 25

Accepted Solution

Tomas Helgi Johannsson earned 350 total points
ID: 36551721

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 :)

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

Hope this helps.
    Tomas Helgi

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 ( 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