Dynamic table name in MySQL

HOW-TO Create a Table with a dynamic name generated at runtime

  1. DECLARE id_val INT(11) ;
  2. DECLARE table_lnk VARCHAR(255) ;
  3. #Some arbitratry sql
  4. SELECT `<column>` INTO id_val FROM `<table>` WHERE `<condition>`;
  5. #Create the new table name
  6. SET @create_query = ‘CREATE TABLE `?` (<column/s> )’;
  7. SET table_lnk=CONCAT(id_val,‘_table’);
  8. SET @create_query =REPLACE(@create_query,‘?’,table_lnk);
  9. #Prepare the create table statement
  10. # preparation is necessary for dynamic table name
  11. prepare create_table_stmt FROM @create_query;
  12. execute create_table_stmt ;
  13. DEALLOCATE PREPARE create_table_stmt;
What the above code does is, it gets a value from a table and based on the value it creates a table with that name ,
to do this we use the replace function to replace the `?`  with the required table name .
Since we cannot directly execute a sql statement stored in a variable we need to prepare it and then execute .
But make sure to deallocate it immidiately after execution

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s