How to generate Insert Statement in MySQL

    A lot of places I saw people asking for ways to generate Insert statements.

    We do have GUI Tool which can extract insert statements for us readily. Some of the times I choose the MySQLDump way to generate insert statements.

    mysqldump -uroot -ppassword --complete-insert --no-create-info DATABASE TABLENAME > TABLENAME.sql

    But mind is very unstable and hungry, we don’t stop at one solution.
    So to remove my mind’s starvation for the Stored Procedure way to extract Insert statement I created following routine.

    As you can see this is really a simple procedure revolves around Information_schema mainly to get details of any table and then fires the simple sql query.

    The procedure I named: InsGen
    Input parameters:

    in_db: Database name of the table for which you want to generate insert statements
    in_table: Tabel name
    in_file: complete file path [eg: C:/mysqlInserts.sql or /var/lib/data/mysqlInserts.sql]

     

    Sample output:

    Generate Inserts mysql
    Generate Inserts mysql

    Download sql script to generate insert statements: InsGen.sql

    ELIMITER $$

DROP PROCEDURE IF EXISTS `InsGen` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsGen`(in_db varchar(20),in_table varchar(20),in_file varchar(100))
BEGIN

declare Whrs varchar(500);
declare Sels varchar(500);
declare Inserts varchar(2000);
declare tablename varchar(20);

set tablename=in_table;
select tablename;
# Comma separated column names - used for Select
select group_concat(concat('concat(\'"\',','ifnull(',column_name,','''')',',\'"\')')) INTO @Sels from information_schema.columns where table_schema=in_db and table_name=tablename;

# Comma separated column names - used for Group By
select group_concat('`',column_name,'`') INTO @Whrs from information_schema.columns where table_schema=in_db and table_name=tablename;

#Main Select Statement for fetching comma separated table values
set @Inserts=concat("select concat('insert into ", in_db,".",tablename," values(',concat_ws(',',",@Sels,"),');') from ", in_db,".",tablename," group by ",@Whrs, " INTO OUTFILE '", in_file ,"'");

PREPARE Inserts FROM @Inserts;
EXECUTE Inserts;

END $$

DELIMITER ;

    I have not considered each and every scenarios yet, but this works for normal tables and it does error if file exists.
    My mind is no more hungry atleast in this regard.

    I hope it helps.

    Related Tags
    8 comments

    2. I also get the error “Create table ‘agps’ failed!
      ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)’ at line 1
      CREATE TABLE `agps` ()” when trying to migrate microsoft access to Mysql on an Ubuntu 10.4 pc.
      I did not write any SQL so it must be some canned SQL script generated by ESF database migration tools kit. which is causing the error.
      I think that the user has all the necessary permissions, but it is almost impossible to tell with mysql.
      Could it be that the version of mysql running under Ubuntu has a different language to that assumed by ESF?
      I am running ESF on a windows pc accessing the access database on the linux pc on a LAN as there is no linux version as far as I know.

      Reply

    3. Hey Mike,
      I tried the demo version!
      I’ve already tried Kettle (the open source product) and ESF Data Migration toolkit (~11K INR) which worked nicely for me.
      +Advance ETL costs (16K INR)!
      My observations.

      Reply

    5. MySQL has a feature for profiling query. By default its off, but you can turn it on per MySQL session by issueing “set profiling=1” on MySQL command prompt.

      http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

      But it has some limitation, first of all, it can be used for debugging while you are in a development mode. Second, it can not be used for query logging purpose.

      Major limitation it has is, you can also store 15 queries in MySQL profile and it can be configure upto 100 queries.

      Reply

    6. As you’ve copied the code from above snipped it seems a problem with quotes (‘ and `). Please download file available at the end of the page and give it a try.

      Regarding not storing output in a file, just remove the section >> ” INTO OUTFILE ‘”, in_file ,”‘”);” from the query and procedure will output the result on prompt.

      Replace “set @Inserts….” line with following:

      set @Inserts=concat(“select concat(‘insert into “, in_db,”.”,tablename,” values(‘,concat_ws(‘,’,”,@Sels,”),’);’) from “, in_db,”.”,tablename,” group by “,@Whrs, “;”);

      Reply

    7. Appreciate your effort. But i am getting the following error using ur SP :

      Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘\’”\’,ifnull(‘,column_name,’,””)’,’,\’”\’)’)) INTO @Sels f’ at line 12

      Cannot figure out the problem. And also is there any way to return the Insert statements as return value instead of writing to a text file ? I am new to MySql

      Reply
    Leave a Reply

    Your email address will not be published. Required fields are marked *