{"id":888,"date":"2010-05-05T16:44:28","date_gmt":"2010-05-05T11:14:28","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=888"},"modified":"2023-09-20T04:55:05","modified_gmt":"2023-09-20T04:55:05","slug":"mysql-stored-procedure-to-generate-extract-insert-statement","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/mysql-stored-procedure-to-generate-extract-insert-statement","title":{"rendered":"How to generate Insert Statement in MySQL"},"content":{"rendered":"\n

A lot of places I saw people asking for ways to generate Insert statements.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

mysqldump -uroot -ppassword --complete-insert --no-create-info DATABASE TABLENAME > TABLENAME.sql<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The procedure I named: InsGen
Input parameters:<\/p>\n\n\n\n
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]<\/p>\n\n\n\n
 <\/p>\n\n\n\n
Sample output:<\/p>\n\n\n
\n
\"Generate<\/a>
Generate Inserts mysql<\/figcaption><\/figure>\n<\/div>\n\n\n
Download sql script to generate insert statements<\/strong>: InsGen.sql<\/a><\/p>\n\n\n\n
ELIMITER $$\n\nDROP PROCEDURE IF EXISTS `InsGen` $$\nCREATE DEFINER=`root`@`localhost` PROCEDURE `InsGen`(in_db varchar(20),in_table varchar(20),in_file varchar(100))\nBEGIN\n\ndeclare Whrs varchar(500);\ndeclare Sels varchar(500);\ndeclare Inserts varchar(2000);\ndeclare tablename varchar(20);\n\nset tablename=in_table;\nselect tablename;\n# Comma separated column names - used for Select\nselect group_concat(concat('concat(\\'\"\\',','ifnull(',column_name,','''')',',\\'\"\\')')) INTO @Sels from information_schema.columns where table_schema=in_db and table_name=tablename;\n\n# Comma separated column names - used for Group By\nselect group_concat('`',column_name,'`') INTO @Whrs from information_schema.columns where table_schema=in_db and table_name=tablename;\n\n#Main Select Statement for fetching comma separated table values\nset @Inserts=concat(\"select concat('insert into \", in_db,\".\",tablename,\" values(',concat_ws(',',\",@Sels,\"),');') from \", in_db,\".\",tablename,\" group by \",@Whrs, \" INTO OUTFILE '\", in_file ,\"'\");\n\nPREPARE Inserts FROM @Inserts;\nEXECUTE Inserts;\n\nEND $$\n\nDELIMITER ;<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
I hope it helps.<\/p>\n","protected":false},"excerpt":{"rendered":"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…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,378],"tags":[24,701,427,702,703,108],"class_list":{"0":"post-888","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-scripts-mysql","8":"tag-code","9":"tag-generate-mysql-inserts","10":"tag-mysql","11":"tag-mysql-insert-statement-generate","12":"tag-mysqldump-insert-only","13":"tag-stored-procedure"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/888","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=888"}],"version-history":[{"count":4,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/888\/revisions"}],"predecessor-version":[{"id":3058,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/888\/revisions\/3058"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=888"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=888"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=888"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}