How to Create One Table From Another Table in SQL
Database:
Operators:
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
.
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:
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' ; |
Here is the result:
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 SELECT INTO
is an easy way to create a new table based on an existing table in the database.