存储过程在MySql中创建插入语句? [英] 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- 表名

2- 列名

3- 列值

如果

1- 表名 = "EMP"

1- Table Name = "EMP"

2- 列名 = "EMPID"

2- Column Name = "EMPID"

3- 列值 = "15"

3- Column Value = "15"

然后输出应该是，选择EMPID为15的所有EMP值一旦为上述条件选择了值，存储过程必须返回用于插入选定值的脚本.

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.

这样做的目的是备份选定的值.当 SP 返回时一个值 {Insert statements}，c# 只会将它们写入 .sql 文件.

The purpose of this is to take backup of selected values. when the SP returns a value {Insert statements}, c# will just write them to a .sql file.

我不知道写这个 SP，任何代码示例都被欣赏了.谢谢..

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

推荐答案

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 ;

这篇关于存储过程在MySql中创建插入语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！