Basic about CREATE TABLE AS Statement – using and example

This SQL tutorial explains how to use the SQL CREATE TABLE AS assertion with syntax and examples.

Description

You can also use the SQL CREATE TABLE AS declaration to create a desk from an existing desk by way of copying the current table’s columns.

It is essential to notice that when developing a table in this way, the new desk will be populated with the archives from the present table (based on the SELECT Statement).

Create Table – By Copying all columns from every other desk

Syntax

The syntax for the CREATE TABLE AS assertion when copying all of the columns in SQL is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

Example

Let’s look at an example that shows how to create a desk with the aid of copying all columns from another table.

For Example:

CREATE TABLE suppliers
AS (SELECT *
    FROM companies
    WHERE id > 1000);

This would create a new desk referred to as suppliers that included all columns from the organizations table.

If there have been archives in the companies table, then the new suppliers table would also include the files chosen by using the SELECT statement.

Create Table – By Copying selected columns from some other desk

Syntax

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

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

Example

Let’s seem to be at an instance that shows how to create a table through copying selected columns from some other table.

For Example:

CREATE TABLE suppliers
  AS (SELECT id, address, city, state, zip
      FROM companies
      WHERE id > 1000);

This would create a new table referred to as suppliers, however the new desk would solely consist of the targeted columns from the corporations table.

Again, if there were data in the agencies table, then the new suppliers desk would additionally comprise the files selected with the aid of the SELECT statement.

Create Table – By Copying chosen 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 appear at an instance that shows how to create a table through copying selected columns from more than one 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 known as suppliers primarily based on columns from both the corporations and categories tables.

Frequently Asked Questions

Question: How can I create a SQL desk from any other desk except copying any values from the historical 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 referred to as suppliers that included all columns from the businesses table, but no records from the businesses table.

Acknowledgements: We’d like to thank Daniel W. for supplying this solution!