MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
1PLs Company - #1Payday.Loans Agency - Loans online and near me $100-$2500 (Same Day)
Powered by MySQL
 
Home / Database Structure / Tables / Question No: 204

How to create table based on existing tables?

Answer No: 204

MySQL provides two ways to create a table based on another table:

  • CREATE TABLE ... SELECT creates a table and populates it from the result set returned by an arbitrary SELECT statement. In this case, the "other table" is the set of rows and columns retrieved by the SELECT.
  • CREATE TABLE ... LIKE creates an empty table using the definition of another existing table.

CREATE TABLE ... SELECT can create a table that is empty or non-empty, depending on what is returned by the SELECT part. The following statements create a table that contains the entire content of the Product table, a table that contains partial content from Product, and an empty copy of Product:

CREATE TABLE Product_Copy1 SELECT * FROM Product;
CREATE TABLE Product_Copy2 SELECT * FROM Product WHERE price > 50;
CREATE TABLE Product_Copy3 SELECT * FROM Product WHERE 0;

Using the LIKE keyword with CREATE TABLE creates an empty table based on the definition of another table. The result is a new table with a definition that includes all column attributes and indexes of the original table. Suppose that table tbl1 looks like this:

CREATE TABLE tbl1 (i INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(i)) ENGINE=InnoDB;

The result of CREATE TABLE ... LIKE differs from the result of using CREATE TABLE ... SELECT to create an empty table. Either of the following statements will create an empty copy of the table tbl1. However, the resulting copies differ in the amount of information retained from the original table structure:

CREATE TABLE tbl1_copy1 SELECT * FROM tbl1 WHERE 0;
CREATE TABLE tbl1_copy2 LIKE tbl1;

If you check structure of above tbl1_copy1 and tbl1_copy2 tables, you will find that the CREATE TABLE ... SELECT statement copies the column name and data type from the original table, but does not retain the PRIMARY KEY index information or the AUTO_INCREMENT column attribute information. The new table also uses the default storage engine, rather than the storage engine utilized by table tbl1. The copy created with CREATE TABLE ... LIKE has none of these problems.

Some table attributes are not copied, even when issuing CREATE TABLE ... LIKE. The most notable examples are:

  • If the original table is a MyISAM table for which the DATA DIRECTORY or INDEX DIRECTORY table options are specified, those options are not copied to the new table. The data and index files for the new table will reside in the database directory for the chosen database.
  • Foreign key definitions in the original table are not copied to the new table. If you wish to retain the foreign key definitions, they must be re-specified with ALTER TABLE after creating the copy.

Related MySQL FAQs to the Above FAQ

How-to-create-table-in-MySQL How to create table in MySQL?

What-is-temporary-table-and-how-to-create-it-in-MySQL What is temporary table and how to create it in MySQL?

How-to-describe-a-table-in-MySQL How to describe a table in MySQL?

How-to-renaming-a-table-in-MySQL How to renaming a table in MySQL?

How-to-specify-multiple-alterations-for-a-table How to specify multiple alterations for a table?

How-to-get-table-metadata How to get table metadata?

How-to-add-new-column-into-an-existing-table-in-MySQL How to add new column into an existing table in MySQL?

How-to-add-a-new-AUTO_INCREMENT-field-to-an-existing-table How to add a new AUTO_INCREMENT field to an existing table?

How-to-drop-a-column-from-the-existing-table How to drop a column from the existing table?

How-to-drop-all-existing-tables-in-one-go How to drop all existing tables in one go?

How-to-get-columns-of-an-existing-table-in-MySQL How to get columns of an existing table in MySQL?

How-to-change-data-type-of-an-existing-column How to change data type of an existing column?

How-to-delete-table-records How to delete table records?

How-to-set-AUTO_INCREMENT-value-other-than-1-in-MySQL How to set AUTO_INCREMENT value other than 1 in MySQL?

How-to-get-copy-of-table-structure-in-MySQL-using-query How to get copy of table structure in MySQL using query?

How-to-get-constraints-or-keys-of-the-existing-table How to get constraints or keys of the existing table?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2023  www.mysqlfaqs.net
All rights reserved.