SQL CREATE TABLE AS

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

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 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

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

Create a table for the employee of a company.

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.


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


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