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;

Output

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

Updated on 30-Jun-2020 13:36:25

• Related Questions & Answers
• Create MySQL query to create a table from an existing table?
• How to use CHAR_LENGTH() in MySQL CREATE TABLE query?
• How to dynamically build MongoDB query?
• Create table query with manual AUTO_INCREMENT start value in MySQL?
• Create table SQL query in SAP HANA
• MySQL: What is 'AUTO_INCREMENT=5' in a create table query?
• MySQL query to display structure of a table
• Create index on create table in MySQL?
• How to create div dynamically using jQuery?
• How to create arrays dynamically in C#?
• MySQL Stored Procedure to create a table?
• How to create a MySQL table with InnoDB engine table?
• How to create a MySQL table with MyISAM engine table?
• Apply MySQL query to each table in a database?
• MySQL query to find alternative records from a table