MySQL export query as insert statements

Question

Hi,

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

You can make something like this:

select 
       concat(
         concat(
           concat(
             concat(
               concat(
                 concat('insert into <table> (requiredTypeID, typeID, quantity) values ("'
                        ,ta.requiredTypeID)
               ,'", "')
             ,ta.typeID)
           ,'", "')
         ,ta.quantity)
       ,'")')
FROM 
       `invtypes` as t, `typeactivitymaterials` as ta 
WHERE 
       volume > 0 AND t.typeID = ta.typeID;
INTO OUTFILE 'file_name'  

I know it is ugly but I think it solves your case :)

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