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