tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities
Share this page:

SQL: CREATE TABLE AS Statement

Learn how to use the SQL CREATE TABLE AS statement with syntax and examples.

Description

You can also use the SQL CREATE TABLE AS statement to 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 SELECT Statement).

Create Table - By Copying all columns from another table

Syntax

The syntax for the SQL CREATE TABLE AS statement copying all of the columns is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

Example

Let's look at an example that shows how to create a table by copying all columns from another 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.

Create Table - By Copying selected columns from another table

Syntax

The syntax for the CREATE TABLE AS statement copying the selected columns is:

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

Example

Let's look at an example that shows how to create a table by copying selected columns from another 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.

Create Table - By Copying selected columns from multiple tables

Syntax

The syntax for the CREATE TABLE AS statement copying 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);

Example

Let's look at an example that shows how to create a table by copying selected columns from multiple tables.

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 a SQL table from another table without copying any values from the old table?

Answer: To do this, the SQL CREATE TABLE 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!