MySQL export query as insert statements

Question

I have two mysql tables which both have a typeID in common. I am wanting to select everything from these two tables with the same typeID, query below:

SELECT ta.requiredTypeID, ta.typeID, ta.quantity 
  FROM `invtypes` as t, `typeactivitymaterials` as ta 
  WHERE volume > 0 AND t.typeID = ta.typeID;

This gives me the correct results but I am trying to export this query as insert statements. I have tried adding INTO OUTFILE "/path/" but this only exports the data as tab/comma delimited data, is it possible to export this data as insert statements?

Cheers

Eef

Answer 1

You might be able to use mysqldump instead. Check out the parameters --tables and --where=.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Answer 2

If you have MySQL Workbench it can generate insert statements for you.

In the SQL editor run your query, then from the result set you can click the export button. Then in the "Save as Type" select as "SQL INSERT statements (*.sql)". Click Save, confirm the table you are exporting and click ok.

If you open the file you should see your result set as insert statements with the column names.

I only tested this on a simple select * from table. I haven't tested this with multiple tables. I hope this helps.

EDIT: Seems like workbench is available for Windows, OSX, and Linux (Thanks vcardillo)

Answer 3

You can combine INSERT with SELECT to insert records directly from the result of a select statement.

The way you do it would be something like this:

INSERT INTO newtable (requiredTypeID, typeID, quantity)
     SELECT ta.requiredTypeID, ta.typeID, ta.quantity 
         FROM `invtypes` as t, `typeactivitymaterials` as ta 
         WHERE volume > 0 AND t.typeID = ta.typeID;

Here's a link to the relevant MySQL manual page.

Note that this would actually do the insert right away; it wouldn't do quite what you asked (which is to generate the insert statements for use later on).