How to Create One Table From Another Table in SQL

Problem

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.

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:

Solution 1

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

Here is the result of the query:

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:

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

Here is the result:

Using SELECT INTO is an easy way to create a new table based on an existing table in the database.