Stored Procedure to create Insert statements in MySql?

I need a storedprocedure to get the records of a Table and return the value as Insert Statements for the selected records.

For Instance, The stored procedure should have three Input parameters...

1- Table Name

2- Column Name

3- Column Value

If

1- Table Name = "EMP"

2- Column Name = "EMPID"

3- Column Value = "15"

Then the output should be, select all the values of EMP where EMPID is 15 Once the values are selected for above condition, the stored procedure must return the script for inserting the selected values.

The purpose of this is to take backup of selected values. when the SP returns a value {Insert statements}, c# will j开发者_JAVA技巧ust write them to a .sql file.

I have no idea about writing this SP, any samples of code is appreicated. Thanks..

You can do this with mysqldump:

mysqldump --no-create-info --skip-triggers 
  --where="$COLUMN_NAME='$COLUMN_VALUE'" --databases $DB --tables $TABLE_NAME

To expand on Anuya's answer (which is referenced from http://kedar.nitty-witty.com/blog/mysql-stored-procedure-to-generate-extract-insert-statement) btw...

First need some helper mysql functions:

/*
isNumeric - return 1/true if passed in string is numeric, false otherwise
Usage example: select isNumeric('2012-02-16'); => 0
*/
DROP FUNCTION IF EXISTS `bettermentdb`.`isNumeric`;
DELIMITER ;;
CREATE DEFINER=`betterment-web`@`localhost` FUNCTION `bettermentdb`.`isNumeric`(s varchar(255))
RETURNS TINYINT
DETERMINISTIC
BEGIN
   SET @match ='^(([0-9+-.$]{1})|([+-]?[$]?[0-9]*(([.]{1}[0-9]*)|([.]?[0-9]+))))$';
   RETURN IF(s regexp @match, 1, 0);
END;;
DELIMITER ;

/*
isNumeric - return an input wrapped in "'" if value is non-numeric, original otherwise.
Depends on isNumeric()
Usage example: select wrapNonNumeric(now()); => '2012-02-16'
           select wrapNonNumeric(NULL); => NULL
           select wrapNonNumeric(1); => 1
*/
DROP FUNCTION IF EXISTS `bettermentdb`.`wrapNonNumeric`;
DELIMITER ;;
CREATE DEFINER=`betterment-web`@`localhost` FUNCTION `bettermentdb`.`wrapNonNumeric`(s varchar(255))
RETURNS varchar(255)
DETERMINISTIC
BEGIN
   RETURN IF(isNumeric(s), s, concat("'", s, "'"));
END;;
DELIMITER ;

Outputs to console with col names defines and non-numeric values wrapped in quotes, while restricting on a given row of input db.table:

DELIMITER ;;
DROP PROCEDURE IF EXISTS GenerateInsertSQL;
CREATE DEFINER=`root`@`localhost` PROCEDURE GenerateInsertSQL(IN in_db varchar(20), IN in_table varchar(32), IN in_row BIGINT)
READS SQL DATA
BEGIN
   DECLARE nullableValues varchar(1000);
   DECLARE colNames varchar(1000);
   DECLARE insertStmnt varchar(2000);

   SELECT group_concat(concat('IFNULL(wrapNonNumeric(`',column_name,'`), "NULL")')) INTO @nullableValues from information_schema.columns where table_schema=in_db and table_name=in_table;
   SELECT group_concat(concat('`',column_name,'`')) INTO @colNames from information_schema.columns where table_schema=in_db and table_name=in_table;

   SET @insertStmnt=concat("select concat('INSERT INTO `", in_db, "`.`", in_table, "`(", @colNames, ") VALUES (', concat_ws(', ',",@nullableValues,"),');') from ", in_db, ".", in_table, " where id = ", in_row, " group by ", @colNames, ";");
   PREPARE insertStmnt FROM @insertStmnt;
   EXECUTE insertStmnt;
END
DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `sample`.`InsGen` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsGen`(
in_db varchar(20),
in_table varchar(20),
in_ColumnName varchar(20),
in_ColumnValue varchar(20)
)
BEGIN

declare Whrs varchar(500);
declare Sels varchar(500);
declare Inserts varchar(200);
declare tablename varchar(20);
declare ColName varchar(20);


set tablename=in_table;


# 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 IGNORE into ", in_db,".",tablename," values(',concat_ws(',',",@Sels,"),');')
 as MyColumn from ", in_db,".",tablename, " where ", in_ColumnName, " = " , in_ColumnValue, " group by ",@Whrs, ";");

 PREPARE Inserts FROM @Inserts;

EXECUTE Inserts;                    

END $$

DELIMITER ;

Had to deal with this issue today.

mysqldump is alright but not too friendly to change the WHERE clause.

I end up SQLyog, a MySQL client, it has a feature where you can export a result set of any sql SELECT statement into a bunch of INSERT statements.