SQL: CREATE a table from another table

You can also create a table from an existing table by copying the existing table's columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SQL SELECT Statement).

Syntax #1 - Copying all columns from another table

The syntax for CREATING a table by copying all columns from another table is:

CREATE TABLE new_table

  AS (SELECT * FROM old_table);

For Example:

CREATE TABLE suppliers

AS (SELECT *

    FROM companies

    WHERE id > 1000);

This would create a new table called suppliers that included all columns from the companies table.

If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #2 - Copying selected columns from another table

The syntax for CREATING a table by copying selected columns from another table is:

CREATE TABLE new_table

  AS (SELECT column_1, column2, ... column_n FROM old_table);

For Example:

CREATE TABLE suppliers

  AS (SELECT id, address, city, state, zip

      FROM companies

      WHERE id > 1000);

This would create a new table called suppliers, but the new table would only include the specified columns from the companies table.

Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #3 - Copying selected columns from multiple tables

The syntax for CREATING a table by copying selected columns from multiple tables is:

CREATE TABLE new_table

  AS (SELECT column_1, column2, ... column_n

      FROM old_table_1, old_table_2, ... old_table_n);

For Example:

CREATE TABLE suppliers

  AS (SELECT companies.id, companies.address, categories.cat_type

      FROM companies, categories

      WHERE companies.id = categories.id

      AND companies.id > 1000);

This would create a new table called suppliers based on columns from both the companies and categories tables.

Frequently Asked Questions

---

Question: How can I create an SQL table from another table without copying any values from the old table?

Answer: To do this, the syntax is:

CREATE TABLE new_table

  AS (SELECT * FROM old_table WHERE 1=2);

For Example:

CREATE TABLE suppliers

  AS (SELECT * FROM companies WHERE 1=2);

This would create a new table called suppliers that included all columns from the companies table, but no data from the companies table.

Acknowledgements: We'd like to thank Daniel W. for providing this solution!