Search This Blog

Wednesday, August 10, 2016

MySQL: This stored procedure inserts 'empty' rows into a specified table

This stored procedure accepts two arguments: a table name and a row count. It inserts the specified number of rows into the specified table. Of course, all columns in the table must either have default values or allow NULL.




DROP PROCEDURE InsertDimension;
DELIMITER $$
CREATE PROCEDURE InsertDimension(IN TableName VARCHAR(50), IN NumRows INT)
    BEGIN
        DECLARE i INT;
        SET i = 1;
        SET @sql_text = concat('INSERT INTO ', TableName, ' VALUES ()' );
        PREPARE stmt FROM @sql_text;
        START TRANSACTION;
        WHILE i <= NumRows DO
            EXECUTE stmt;
            SET i = i + 1;
        END WHILE;
        COMMIT;
        DEALLOCATE PREPARE stmt;
    END$$
DELIMITER ;
Post a Comment