A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be selected.

Create Table Using Another Table

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

Syntax

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

Create Table Using Another Table Example

Consider we have following existing table:

  create table users(
        id  int(3) primary key,
        name varchar(20),
        email varchar(20),
        country varchar(20),
        password varchar(20)
  );

The following SQL creates a new table called "usersNew" (which is a copy of the "users" table):

CREATE TABLE usersNew AS
SELECT id, name, email, country, password
FROM users;