MySQL query to create table dynamically?

For this, you can use stored procedure. Let us create a table dynamically with two columns i.e. StudentId as int, whereas StudentName as varchar −

mysql> DELIMITER $$
mysql> CREATE PROCEDURE creatingDynamicTableDemo(yourTableName
VARCHAR(200))
   -> BEGIN
   ->    SET @name = yourTableName;
   ->    SET @st = CONCAT('
   '>       CREATE TABLE IF NOT EXISTS `' , @name, '` (
   '>       `StudentId` int UNSIGNED NOT NULL AUTO_INCREMENT,
   '>       `StudentName` varchar(20) NOT NULL,
   '>    PRIMARY KEY (`StudentId`)
   '>    )
   '> ');
   -> PREPARE myStatement FROM @st;
   -> EXECUTE myStatement;
   -> DEALLOCATE PREPARE myStatement;
   -> END $$
Query OK, 0 rows affected (0.16 sec)
mysql> DELIMITER ;

Above, we have set the StudentId as PRIMARY KEY.

Call stored procedure with the help of CALL command −

mysql> call creatingDynamicTableDemo('DemoTable');
Query OK, 0 rows affected, 1 warning (0.65 sec)

Now you can check the description of the table −

mysql> desc DemoTable;

This will produce the following output −

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| StudentId   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| StudentName | varchar(20)      | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Sharon Christine

Published on 11-Jul-2019 17:26:44