CODEWITHSUNDEEP
mysql
akshay
akshay

Reputation: 31

How to create a create table statement in a loop in mysql

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, ...

Upvotes: 3

Views: 3902

Answers (2)

Julian Leite
Julian Leite

Reputation: 112

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;

Upvotes: -1

1000111
1000111

Reputation: 13519

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:

enter image description here

N:B: Procedure execution might take several seconds. Don't be impatient.

Upvotes: 2

Related Questions