How to Create One Table From Another Table in SQL

You would like to create a new table with data copied from another table.

Example:

Our database has a table named product with data in the following columns: id (primary key), name , category , and price .

id	name	category	price
105	rose	flower	5.70
108	desk	furniture	120.00
115	tulip	flower	6.50
123	sunflower	flower	7.50
145	guitar	music	300.00
155	orchid	flower	9.50
158	flute	music	156.00

In the database, let’s create a new table named florist which will store the following columns: id , name , and price . These columns come from the table product but only from the category flower.

It is important to note that we are creating a new table. The table florist doesn’t exist in this database.

The CREATE TABLE AS SELECT Structure

To create a new table from another table, you can use CREATE TABLE AS SELECT . This construction is standard SQL. Look at the SQL code below:

Learn how to create and manage tables, views, and indexes in SQL databases

Solution 1:

CREATE TABLE florist AS SELECT * FROM product WHERE category = 'flower';

Here is the result of the query:

id	name	category	price
105	rose	flower	5.70
115	tulip	flower	6.50
123	sunflower	flower	7.50
155	orchid	flower	9.50

Using CREATE TABLE , you can create a new table by copying data from another table. In this case, we first use the CREATE TABLE clause with the name for new table (in our example: florist ), we next write AS and the SELECT query with the names of the columns (in our example: * ), and we then write FROM followed by the name of the table from which the data is gathered (in our example: product ). Then, you can use any SQL clause: WHERE , GROUP BY , HAVING , etc.

The new table florist will contain the definition of the columns from the product table ( id , name , category , and price ). The number of rows is limited by using a WHERE clause, filtering the records to only retrieve data from the category flower.

The SELECT INTO Structure

Another solution is to use SELECT INTO . This syntax is non-standard SQL, but it’s supported by many popular databases.

Solution 2:

SELECT id, name, price INTO florist FROM product WHERE category = 'flower';

Here is the result:

id	name	price
105	rose	5.70
115	tulip	6.50
123	sunflower	7.50
155	orchid	9.50

Discussion:

If you would like to create a new table based on the structure and data from another table, you can use the SELECT INTO clause. First, write a SELECT clause followed by a list of columns (in our example: id , name , and price ) from the existing table (in our example: product ).

Notice that there are more columns in the table product . We only selected the columns we’re interested in.

Next, use the keyword INTO with the name of the new table you want to create (in our example: florist ). Then, write the keyword FROM with the name of the existing table (in our example: product ).

If you would like to select filtered rows from the table, use the WHERE clause. After WHERE , write the conditions to filter the data (in our example: WHERE category=’flower’ ).

In this example, we are creating a new table florist which has less columns than the table product (the difference is the column category). This new table also has fewer rows – only the rows with the category flower.

Of course, if you want to create a table using all of the columns in the other table, you can use * instead of listing the columns after SELECT . See the example below:

Solution 3:

SELECT * INTO florist FROM product WHERE category = 'flower';