Create a table like another table or from a select query


There are some cases where we need to create a table similar or exactly same as another table. The easiest and best way is use create table along with additional parameters. We can create a table similar to another table without data and with data.

  • Create a similar table without the data from the orginal table:
    To create a table schema without copying the data of the source table we use command CREATE TABLE … LIKE . What this command does is, it creates a new table exactly similar to the old table with no data of the old table. It also doesn’t create the indexes present in the old table. This we need to do manually after creating the table. The statement for this is as follows:

     CREATE TABLE new_table LIKE old_table;
  • Create a similar table along with data:
    To create a table along with data that is similar to a table or create a table from the result of a select query. The command creates a table that can hold the data the select returns. It converts the auto increment to int , varchar to char. The power of this command lies in the ability to create a table combining two table or part of a table or data of a specific condition. All this can be done as it accepts just a select query. The format for this is as follows:

      CREATE TABLE bar SELECT n FROM foo; 

    The various implementations of it is as follows:

     CREATE TABLE artists_and_works
      SELECT artist.name, COUNT(work.artist_id) AS number_of_works
      FROM artist LEFT JOIN work ON artist.id = work.artist_id
      GROUP BY artist.id;
    CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo; 
    CREATE TABLE bar (m INT) SELECT n FROM foo;

, , , , , , , ,

  1. No comments yet.
(will not be published)