Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

create table products_bk
as
select * from products

The SQL code above creates a backup table called Products_bk which contains all the data from Products table. Then you can proceed with any changes you want to do with Products table. If anything goes wrong, you can recreate the table from the backup, both structure and data wise.

In MySQL, you can create a new table and data from an existing table, it's sometimes also called clone or copy a table. In essence, you can create a new table from an existing table by adding a SELECT statement at the end of the CREATE TABLE statement.

For more information, check MySQL manual for the version you use. CREATE TABLE ... SELECT Syntax

The basic syntax is:

CREATE TABLE [IF NOT EXISTS] new_tbl [AS] SELECT * FROM existing_tbl;

It creates the new table first, then inserting data into it based on the columns used in the SELECT statement from an existing table.

There are a few variations of the basic syntax when used.

1. Create an exact copy of an existing table.

This is the simplest way of creating a table. No table structure syntax (columns, data types, constraints, etc.) is involved. The new table will simply be created by using the same structure of the existing table. The new table will also contain all the data from the existing table.

create table products_bk
as
select * from products

2. Create a new table based on one or more existing tables.

Here we create a new table called product_sold_by_order for products in each order and the total sales of each product.

The columns created in the new table are all sourced from the column definitions in the SELECT statement. MySQL determines the best data type to use for calculated columns, e.g. the TotalSales column. The SELECT query can be simple or complex, depending on your need, such as using WHERE, JOIN, UNION, GROUP BY, HAVING, etc.

create table product_sold_by_order
as
select distinct y.OrderID, 
    y.ProductID, 
    x.ProductName, 
    y.UnitPrice, 
    y.Quantity, 
    y.Discount, 
    round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2) as TotalSales
from Products x
inner join Order_Details y on x.ProductID = y.ProductID
order by y.OrderID;

3. Create a new table based on one or more existing tables, and at the same time create extra new column(s).

The query below is similar to the one shown above, but in the create table statement, it has specified a new column to be created with data type, not null constraint, and auto_increment attribute. In the new table product_sold_by_order2, all the columns in the SELECT statement are appended to the right side of the extra new column ID. This gives us the flexibility to add extra new columns without having to write and run another ALTER table query after the new table is created.

create table product_sold_by_order2(ID int not null auto_increment, PRIMARY KEY (ID))
as
select distinct y.OrderID, 
    y.ProductID, 
    x.ProductName, 
    y.UnitPrice, 
    y.Quantity, 
    y.Discount, 
    round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2) as TotalSales
from Products x
inner join Order_Details y on x.ProductID = y.ProductID
order by y.OrderID;

Things to note:

• The keyword AS is optional but it's always a good idea to use it in order to be consistent with the same type of query used by other database engine such as Oracle, PostgreSQL, SQLite, etc.

• Creating table this way does not automatically create any indexes on the new table. If you need indexes on the new table, specify them before the SELECT statement, similar to specifying PRIMARY KEY (ID) in the example above, e.g. you might specify INDEX(ProductName) or KEY(ProductName) which creates an index on ProductName column. KEY is normally a synonym for INDEX.

  Alternatively, you can create indexes separately after the table is created.

• Be careful when using the keywords IF NOT EXISTS, in MySQL versions before 5.5.6, if the destination table exists in the database, it inserts rows to the table if keywords IF NOT EXISTS are used. This may not be what you want. Since MySQL 5.5.6, if IF NOT EXISTS is given and the target table exists, nothing is inserted into the destination table.

  So to avoid any unexpected result or side-effect, before using CREATE TABLE ... SELECT statement, make sure that:

  (1) you check the version of the MySQL you are using.

  (2) the destination table does NOT exist. If it does exist, rename or delete it. Then after the destination table is created, check its structure to ensure everything you want is there. If not, modify the table structure to suit your needs.

  Alternatively, you can break table creation into 2 clear-cut steps - create the table first, then populate data into it.

  Method #1:

  Use CREATE TABLE IF NOT EXISTS in a statement, then INSERT ... SELECT in another statement rather than CREATE TABLE IF NOT EXISTS ... SELECT in one statement.

  Create the table first.

  create table ProductsAboveAvgPrice
  (
      ProductName varchar(40) not null,
      UnitPrice double not null
  )

  Then add data to the new table.

  insert into ProductsAboveAvgPrice
  select distinct ProductName, UnitPrice
  from Products
  where UnitPrice > (select avg(UnitPrice) from Products)
  order by UnitPrice

  Method #2:

  Create a table structure from an existing table by using CREATE TABLE ... LIKE, then INSERT ... SELECT in another statement.

  First, use CREATE TABLE ... LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table.

  create table employees_copy like employees

  Now insert data into table employees_copy.

  insert into employees_copy
  select * from employees

  Note:

  - CREATE TABLE ... LIKE statement works only for base tables, not for views.

  - If the original table is a TEMPORARY table, CREATE TABLE ... LIKE does not preserve TEMPORARY, which means the empty table created will be a permanent table rather than a temporary table. To create an empty TEMPORARY table, use CREATE TEMPORARY TABLE ... LIKE.

  The following statement creates a temporary table for products above average price.

  create temporary table tempProductsAboveAvgPrice
  as
  select distinct ProductName, UnitPrice
  from Products
  where UnitPrice > (select avg(UnitPrice) from Products)
  order by UnitPrice

  Then if you create an empty table based on the structure of the temporary table tempProductsAboveAvgPrice, the empty table will be created as a permanent table in Northwind database. The table can be seen if you run the SHOW TABLES statement.

  create table tempProductsAboveAvgPrice_copy like tempProductsAboveAvgPrice

  To create a TEMPORARY empty table, use CREATE TEMPORARY TABLE ... LIKE. This way, empty table tempProductsAboveAvgPrice_copy2 will not become part of the Northwind database schema.

  create temporary table tempProductsAboveAvgPrice_copy2 like tempProductsAboveAvgPrice

Happy Coding!

Other tutorials in this category

1. Create MySQL table by using CREATE TABLE statement

2. Create MySQL temporary table

3. How to disable (or enable) Safe Updates in MySQL

4. MySQL constraints explained by examples

Back to Tutorial Index Page