laika222
10/27/2016 - 4:34 PM

Creates a new table with the exact same columns (include column types) as the old table, and copies all data rows into it (lines 1 and 2).

Creates a new table with the exact same columns (include column types) as the old table, and copies all data rows into it (lines 1 and 2). Lines 5-7 show how to duplicate a table and copy over only certain rows from the source table. This creates new columns with the same data types and names as are present in the original table - however, it does not copy over the metadata for the columns (Primary Key, Foreign Key, AUTO_INCREMENT, ON UPDATE, etc.), so that has to all be re-declared for the copied table once it is created.

-- Creates a table and populates it with the results set from the SELECT query - the column names and data types will be copied over to the Orders5 table, however the metadata such as Primary Key will not be copied over and will need to be re-declared after the fact.
CREATE TABLE Orders5
SELECT * from Orders2 WHERE Product = 'Baseball Bat';

-- Creates a table Orders4 copying over the entire table Orders2 (since it uses a SELECT * statement)
CREATE TABLE Orders4
SELECT * from Orders2;