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.

This syntax is non-standard SQL, but it’s supported by many popular databases. The SELECT INTO StructureĪnother solution is to use SELECT INTO. The number of rows is limited by using a WHERE clause, filtering the records to only retrieve data from the category flower. The new table florist will contain the definition of the columns from the product table ( id, name, category, and price). Then, you can use any SQL clause: WHERE, GROUP BY, HAVING, etc. 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). Using CREATE TABLE, you can create a new table by copying data from another table. To create a new table from another table, you can use CREATE TABLE AS SELECT. The table florist doesn’t exist in this database. It is important to note that we are creating a new table. These columns come from the table product but only from the category flower. In the database, let’s create a new table named florist which will store the following columns: id, name, and price. Our database has a table named product with data in the following columns: id (primary key), name, category, and price. You would like to create a new table with data copied from another table.

Mysql create table from existing table