Creating a table from an existing MySQL table using SQL Query

INTRODUCTION

Sometimes when you start with a new project, you may want to create a new table which may be very similar to some table in the same database, or other. It may be combination of several tables, or a self-join, along with the data as well. Instead of creating the table, and then filling the data, or using export and import features of MySQL, we can use SQL Queries to do the job for us.
In this article I will be talking about this with a couple of examples

The syntax of creating a table with SQL Query is as follows

CREATE TABLE new_tbl
AS SELECT * FROM orig_tbl;


Example 1 Creating a subset of a larger table

Lets assume we have a very big table called Cities which has about 147110 rows. It contains almost all the cities information of the world.

For some new application, we want a table called citys with only county code 233 which is for the United States of America. The following SQL statement will do the trick

CREATE TABLE citys
AS SELECT * FROM cities
WHERE country_id = 233;

If we peek into the newly created citys table, we will find the cities of USA only in this newly created table.

Example 2 – Creating table with data from several tables

We will recreate the citys table again but this time we will add more information using two more tables states and countries to fill in the details rather than just the codes. We will restrict our table columns to id, name, state_name, country_name
Here we are joining the three tables’ cities, states and country, and creating a new table called citys, with new names for the columns as well.

CREATE TABLE citys
AS
SELECT c.id, c.name as city_name, s.name as state_name, ct.name as country_name FROM cities c
LEFT JOIN states s ON c.state_id = s.id
LEFT JOIN countries ct ON c.country_id = ct.id
WHERE c.country_id = 233;


Let’s see how our new table looks like now

Note : When creating a table with CREATE TABLE ... SELECT, make sure to alias any function calls or expressions in the query. If you do not, the CREATE statement might fail or result in undesirable column names. In the above example since we used three tables and there were many columns (especially the id and names) common in these tables, we made sure we aliased the column names to specify very clearly which columns we are referring to.

Example 3 – Creating a table which exists in another database

If we want to create a new table in a new database, but the tables are existing in another database in the same server, then all we need to do is to include the database name as well in front of the table names.

CREATE TABLE new_tbl
AS SELECT * FROM database.orig_tbl;


To create our citys table in another database (assume the three tables are in a database with name newdb)

CREATE TABLE Citys
AS
SELECT c.id, c.name as city_name, s.iso2 as state_code, s.name as state_name,
ct.iso2 as country_code, ct.iso3 as country_iso3, ct.name as country_name,
ct.phonecode as phonecode, ct.currency as currency, ct.region as region,
ct.subregion as subregion
FROM newdb.cities c
LEFT JOIN newdb.states s ON c.state_id = s.id
LEFT JOIN newdb.countries ct ON c.country_id = ct.id
WHERE c.country_id = 233;


And we should find our newly created citys table in the current database

INDEXES

The city table we just created has about 19,818 rows. If we inspect the structure of the table, you will notice that there is no index on the newly created table!

CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement:

So let recreate the table, but this time we will specify to create the indexes as well. Let us see how

CREATE TABLE Citys (PRIMARY KEY (id))
AS
SELECT c.id, c.name as city_name, s.iso2 as state_code, s.name as state_name,
ct.iso2 as country_code, ct.iso3 as country_iso3, ct.name as country_name,
ct.phonecode as phonecode, ct.currency as currency, ct.region as region,
ct.subregion as subregion
FROM newdb.cities c
LEFT JOIN newdb.states s ON c.state_id = s.id
LEFT JOIN newdb.countries ct ON c.country_id = ct.id
WHERE c.country_id = 233;


Now you will find the new table with a PRIMARY KEY index as well

Hope you enjoyed this article on how to create a new table from existing ones, with the help of SQL queries.