SQL CREATE TABLE AS Statement with Examples

Learn how to use the SQL CREATE TABLE AS statement with the explained examples given in this tutorial. You can create a table using the SQL CREATE TABLE AS statement and the old created table.

You can use the column names of another table and use it to create the new table with the same column name. The newly created table also contains the data records from the old table.

If you want to create a table and the columns of the table match the previously created table. In that condition, you do not need to specify the columns and the data type again for the new column. You just need to use the SQL CREATE TABLE AS statement and select only the old tables with the required column name.

How to Use SQL CREATE TABLE AS Statement

To create a new table, you need to find the new table columns matches with the required table. If all the columns match with the table, you use all the columns of the old table to create a new table.

Let’s create a table with the syntax and examples given below for each type.

Create Table By Copying All the Columns From Another Table

You have to use the SQL SELECT statement to select the columns you want to use and create the new table. Apply the condition for the data you want to get from the old table. If the old table contains the records or some data, the newly created table also copy the data from the old table.

Syntax

CREATE TABLE new_tablename AS(
SELECT * from old_tablename where condition;
);

CREATE TABLE new_tablename AS(

SELECT * from old_tablename where condition;

);

This example can be useful as you don’t need to specify the columns names with the data type. The above syntax can contain the WHERE condition to copy all the required records from the old table. In addition to this, you also don’t need to insert each record one by one.

Create Table By Copying Only The Selected Columns From Another Table

You can specify only those column names which you want to copy to the new table. To specify the column names, you need to only the required column names in the syntax given below. You can include as many column as you want.
Syntax

CREATE TABLE new_tablename AS(
SELECT column1, column2, column3...columnN 
from old_tablename;
);

CREATE TABLE new_tablename AS(

SELECT column1, column2, column3...columnN

from old_tablename;

);

Create Table By Copying Selected Columns From Multiple Tables

In addition to all the above syntax, you can also create the new table from the multiple numbers of old tables. If you want to create a new table and all the columns not matching with the single old table. You can use multiple tables to copy the columns from each one.

Syntax

CREATE TABLE new_tablename AS(
SELECT column1, column2, column3...columnN 
from old_tablename1, old_tablename2, old_tablename3...old_tablenameN;
);

CREATE TABLE new_tablename AS(

SELECT column1, column2, column3...columnN

from old_tablename1, old_tablename2, old_tablename3...old_tablenameN;

);

Specify the column name and the table name from the old table you have already created and want to use for the new table.

The parameter of the Syntax given above

Sr.No.	Parameter Name	Description
1	new_tablename	Specify the name of the new table that you want to create.
2	old_tablename	Specify the table name that you have already created and want to create the new table with the selected columns from this table.
3	old_tablename1, old_tablename2, old_tablename3…old_tablenameN	Enter the tables names from which you want the columns for the new table you want to create. You can specify N number of tables for the N number of columns.
4	column1, column2…columnN	Enter the column name you want to create for the respected table. You can create N number of column for the N number of tables. The columns should match with the respected table.

Examples to Use SQL CREATE TABLE AS Statement

Example1: Create Table By Copying All the Columns From Another Table

Create a table for the employee of a company.

CREATE TABLE Employee AS(
SELECT * from Company where salary>25000;
);

CREATE TABLE Employee AS(

SELECT * from Company where salary>25000;

);

The above example selects all the columns from the table Company. Because here I have also specified the condition, it will get all the record for that condition.

Example2: Create Table By Copying Only The Selected Columns From Another Table

CREATE TABLE Employee AS(
SELECT firstname, lastname, address, email from Company where salary>25000;
);

CREATE TABLE Employee AS(

SELECT firstname, lastname, address, email from Company where salary>25000;

);

The above example copy only the columns you specified and the record for which you have given the condition.

Example3: Create Table By Copying Selected Columns From Multiple Tables

CREATE TABLE Employee AS(
SELECT address, designation, category_type
from Person, Company, Student
where id>700;
);

CREATE TABLE Employee AS(

SELECT address, designation, category_type

from Person, Company, Student

where id>700;

);

The example copies the columns from the three different tables for the three different column. It also copies the data from these table for the id greater than 700.

You must also learn.

Reference

SQL Creating a Table As Doc.

SQL Tutorial

SQL CREATE TABLE AS

How to Use SQL CREATE TABLE AS Statement

Create Table By Copying All the Columns From Another Table

Create Table By Copying Only The Selected Columns From Another Table

Create Table By Copying Selected Columns From Multiple Tables

Examples to Use SQL CREATE TABLE AS Statement

Related Posts: