Back to list Standard SQL How to Create One Table From Another Table in SQL Database: SQL MySQL PostgreSQL Oracle SQL Server SQLite Operators:SELECT INTO, CREATE TABLE AS SELECT 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. idnamecategoryprice 105roseflower5.70 108deskfurniture120.00 115tulipflower6.50 123sunflowerflower7.50 145guitarmusic300.00 155orchidflower9.50 158flutemusic156.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: idnamecategoryprice 105roseflower5.70 115tulipflower6.50 123sunflowerflower7.50 155orchidflower9.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: idnameprice 105rose5.70 115tulip6.50 123sunflower7.50 155orchid9.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 2: SELECT * INTO florist FROM product WHERE category=’flower’; Here is the result: idnamecategoryprice 105roseflower5.70 115tulipflower6.50 123sunflowerflower7.50 155orchidflower9.50 Using SELECT INTO is an easy way to create a new table based on an existing table in the database. Recommended courses: The Basics of Creating Tables Data Types in SQL SQL Constraints Recommended articles: How to Create a Table in SQL Referential Constraints and Foreign Keys in MySQL Understanding Numerical Data Types in SQL See also: Tags: SQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.