I am trying to create a procedure which will fill up the table until certain amount of elements.
At the current moment I have
CREATE PROCEDURE PopulateTable(
IN dbName tinytext,
IN tableName tinytext,
IN amount INT)
BEGIN
DECLARE current_amount INT DEFAULT 0;
SET current_amount = SELECT COUNT(*) FROM dbName,'.',tableName;
WHILE current_amount <= amount DO
set @dll=CONCAT('INSERT INTO `',dbName,'`.`',tableName,'`(',
'`',tableName,'_name`) ',
'VALUES(\'',tableName,'_',current_amount,'\')');
prepare stmt from @ddl;
execute stmt;
SET current_amount = current_amount + 1;
END WHILE;
END;
So what I am trying to do is, once user calls the procedure, the function will check and see how many current elements exist, and fill up the remaining elements.
First problem I have is that I do not know how to count the elements, so my SELECT COUNT(*) FROM dbName,'.',tableName;
does not work.
I also want to a suggestion since I am kind of new to databases if what I am doing is correct or if there is a better way to do this?
Also just if this is of any help the table I am trying to do this to only has 2 fields, one of them being id, which is auto incremented and is primary and the other being profile_name which I am populating.
Thanks to anyone for their help!
Firstly, I think you'll have a delimiter problem if you try to execute the code you pasted. The procedure declaration delimiter has to be different from the one you use into the procedure code (here ';'). You will have to use DELIMITER statement;
Your procedure belongs to a schema; I'm not sure you can query tables from other shemas (especially without USE statement);
This is not a good idea if your database contains tables whitch are not supposed to be populated through your procedure;
If you have a limited number of concerned tables, I think it will be a better idea to define one procedure for each table. In this way, table name will be explicit in each procedure code and it will avoid the use of prepared statements;
Be careful about you 'amount' parameter : are you sure that your server can handle requests if I pass the maximum value for INT as amount ?
I think you should use '<' instead of '<=' in your WHILE condition
If you want to insert a large number of lines, you'll obtain better performances performing "grouped" inserts, or generating a temporary table (for example with MEMORY engine) containing all your lines and performing a unique INSERT selecting your temporary table's content.