I want to create multiple tables with just one statement (query).
Loop
start
(
create table a
)
a =a +1
end loop
So say it has to create 100 tables labeled as TABLE1, TABLE2, ...
-
You might want to explain a bit more...Sami Kuhmonen– Sami Kuhmonen2016-02-07 05:15:24 +00:00Commented Feb 7, 2016 at 5:15
-
100 tables same column names and datatype ?Vadivel S– Vadivel S2016-02-07 05:29:45 +00:00Commented Feb 7, 2016 at 5:29
-
what will be the attributes of those created tables?1000111– 10001112016-02-07 05:30:46 +00:00Commented Feb 7, 2016 at 5:30
-
Yes, that is correct of the same variablesakshay– akshay2016-02-07 05:30:54 +00:00Commented Feb 7, 2016 at 5:30
-
create table 1 ( name varchar (70), age int(10) )akshay– akshay2016-02-07 05:31:35 +00:00Commented Feb 7, 2016 at 5:31
|
Show 1 more comment
2 Answers
Try the following procedure.
DROP PROCEDURE IF EXISTS `createTableProcTest`;
delimiter //
CREATE PROCEDURE `createTableProcTest`()
BEGIN
DECLARE count INT Default 0;
simple_loop: LOOP
SET @a := count + 1;
SET @statement = CONCAT('Create table Table',@a,' ( name VARCHAR(70), age int );');
PREPARE stmt FROM @statement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET count = count + 1;
IF count=100 THEN
LEAVE simple_loop;
END IF;
END LOOP simple_loop;
END//
In order to execute just do the following:
Call createTableProcTest();
By executing the above procedure 100 tables will be created having name table1,...,table100. And the table structure would look like following:
N:B: Procedure execution might take several seconds. Don't be impatient.
Comments
You need give us more details but, I think is easier to call a stored procedure, inside the loop, to create the procedure.
You need to create a procedure to create the tables you need and call this procedure inside the loop.
Ex:
CREATE PROCEDURE SP_Create_Table(IN tableName VARCHAR(50)) BEGIN SET @sql = CONCAT('CREATE TABLE ', tableName, '(column1 INT(11))');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END
Now, call the create table procedure inside the loop
DELIMITER // CREATE FUNCTION CalcIncome ( starting_value INT ) RETURNS INT BEGIN
DECLARE income INT; SET income = 0; label1: WHILE income <= 50 DO call SP_Create_Table(CONVERT(VARCHAR(50),starting_value)); END WHILE label1; RETURN income; END; // DELIMITER;
