【mysql】存储过程批量插入数据

一、利用临时表实现动态插入数据

DELIMITER $$
CREATE PROCEDURE insert_data_sp(id_begin INT,id_end INT)
BEGIN

DECLARE i INT;

#如果存在该表，删除该表
DROP TABLE IF EXISTS patient_info;

#创建表
CREATE TABLE IF NOT EXISTS patient_info(
brid00 VARCHAR(50) PRIMARY KEY COMMENT '用户唯一识别码',
brxm00 VARCHAR(30) NOT NULL COMMENT '用户姓名',
brlxdh VARCHAR(30) COMMENT '用户电话号码',
sex VARCHAR(30) COMMENT '性别',
certype VARCHAR(30) COMMENT '证件类型',
certid VARCHAR(30) COMMENT '证件号码',
birth CHAR(8) COMMENT '出生日期'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#创建新的临时表
DROP TEMPORARY TABLE IF EXISTS test_demo.a_temp;
CREATE TEMPORARY TABLE IF NOT EXISTS test_demo.a_temp(
c_id VARCHAR(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#往临时表插入一条原始数据
INSERT INTO test_demo.a_temp VALUES('1');

SET i = id_begin;
WHILE(i<=id_end) DO
		UPDATE test_demo.a_temp SET c_id = i;
		INSERT into test_demo.patient_info select c_id as brid00,'用户',&