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)
