Exporting Results of a MySQL Query as INSERTS
Another technical post, mostly for my own future reference.
tl;dr If you only have SELECT
privileges, you may be out of luck. Otherwise create
a new table with the results of your query and export that.
Here we have another case of my wanting to do things of a “data science” flavor when much of SQL and the advice about using SQL is more of a “run a production application” flavor.
We wanted to share some data with another team that has their own database. This other
team does not – and should not – have full access to our database. Additionally, some
of the fields in our database are filled with all sorts of escape characters and
punctuation that would not fare well as a CSV export. So we wanted to export the results
of our query as a bunch of INSERT
statements so that the other team could import this
information into their database.
You would thing that the GUI that we use for writing and debugging our queries would
have a button to click for exporting the results of a query as SQL. It does, but only
in the case that all the data comes from one table. That is, I can export the results
of SELECT foo FROM bar WHERE baz > 0
as a bunch of INSERT statements. But I can not
do it when my SELECT
statement involves data from more than one table in my database.
But what I can do is CREATE TABLE
with the results of my query and then export that.
The MySQL documentation (5.5, 8.0) gives the following example:
CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;
You can then export this as a bunch of INSERT statements and send it to your friends!
To be perfectly honest, I didn’t realize that you could do it in one step, and I created a view from the query and then created a table from the view. That definitely worked.
I was fortunate that I have my own test instance of the database so that I could create a new table without bothering anyone (and can drop it at my convenience).