CREATE TABLE FROM SELECT

You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE stack (
    id_user INT,
    username VARCHAR(30),
    password VARCHAR(30)
);

Create a table in the same database:

-- create a table from another table in the same database with all attributes
CREATE TABLE stack2 AS SELECT * FROM stack;

-- create a table from another table in the same database with some attributes
CREATE TABLE stack3 AS SELECT username, password FROM stack;

Create tables from different databases:

-- create a table from another table from another database with all attributes
CREATE TABLE stack2 AS SELECT * FROM second_db.stack;

-- create a table from another table from another database with some attributes
CREATE TABLE stack3 AS SELECT username, password FROM second_db.stack;

N.B

To create a table same of another table that exist in another database, you need to specifies the name of the database like this:

FROM NAME_DATABASE.name_table

Found a mistake? Have a question or improvement idea? Let me know.

Table Of Contents