MySQL CREATE TABLE SELECT

In this blog post, we will build on a previous post, where we cloned a new table with identical structure from an existing one, yet with no data. Leveraging CREATE TABLE SELECT syntax, we can take this a step further by populating the new table with data from the source table.


Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.


With the 2017 holiday season upon us, this will be the last blog post of the year. I have had a great year in many aspects of life and am excited to be closing out this year strong, with a focus on an even stronger 2018. I truly hope you and yours have a wonderful and safe holiday season, wherever you are in life and the world. With that said the initial blog post for 2018 from Digital Owl’s Prose will be on the first Wednesday of January from which, I relish continuing to blog my passion. Hope to see you there and now, on to this blog post…

To start, I will grab a description of the friends source table along with a count of the current number of records present:

mysql> DESC friends;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| friend_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| f_first_name | varchar(30) | YES  |     | NULL    |                |
| f_last_name  | varchar(30) | YES  |     | NULL    |                |
| f_cell_phone | char(12)    | YES  |     | NULL    |                |
| f_age        | int(11)     | YES  |     | NULL    |                |
| f_bday       | date        | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
mysql> SELECT COUNT(*) FROM friends;
+----------+
| COUNT(*) |
+----------+
|       30 |
+----------+
1 row in set (0.01 sec)

We Want Data!

Can you make another table similar to friends with structure and data?
Yep sure can.
Have a look.

mysql> CREATE TABLE some_friends
-> SELECT * FROM friends;
Query OK, 30 rows affected (0.57 sec)
Records: 30 Duplicates: 0 Warnings: 0

That was straightforward enough. Let’s fetch a count of the records from the new some_friends table:

mysql> SELECT COUNT(*) FROM some_friends;
+----------+
| COUNT(*) |
+----------+
|       30 |
+----------+
1 row in set (0.00 sec)

That worked nicely. Same number of records as the friends table.
However, I want to dig in to both tables’ structure a bit more in detail for a moment.

mysql> SHOW CREATE TABLE friends\G;
*************************** 1. row ***************************
Table: friends
Create Table: CREATE TABLE `friends` (
`friend_id` int(11) NOT NULL AUTO_INCREMENT,
`f_first_name` varchar(30) DEFAULT NULL,
`f_last_name` varchar(30) DEFAULT NULL,
`f_cell_phone` char(12) DEFAULT NULL,
`f_age` int(11) DEFAULT NULL,
`f_bday` date DEFAULT NULL,
PRIMARY KEY (`friend_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE more_friends\G;
*************************** 1. row ***************************
Table: more_friends
Create Table: CREATE TABLE `more_friends` (
`friend_id` int(11) NOT NULL AUTO_INCREMENT,
`f_first_name` varchar(30) DEFAULT NULL,
`f_last_name` varchar(30) DEFAULT NULL,
`f_cell_phone` char(12) DEFAULT NULL,
`f_age` int(11) DEFAULT NULL,
`f_bday` date DEFAULT NULL,
PRIMARY KEY (`friend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Pretty much mirror images of one another.


Here’s something to keep in mind though. Imagine we use the following CREATE TABLE statement to make a new table.

mysql> CREATE TABLE names(
-> name_id INTEGER AUTO_INCREMENT NOT NULL,
-> first_name VARCHAR(30) NOT NULL,
-> age INTEGER,
-> phone_num CHAR(12) NOT NULL,
-> PRIMARY KEY (name_id));

And just to have some data present, I will INSERT three records into the names table,

mysql> INSERT INTO names(first_name,age,phone_num)
-> VALUES
-> ('Jim', 22, '454-987-2219'),
-> ('Tom', 43, '873-010-7433'),
-> ('Gretchin', 38, '590-191-5545');
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0

Suppose this table will have many rows and it will be used time and again to look-up the phone_num column value so for sure, that column needs an index.

mysql> CREATE INDEX phone_book ON names (phone_num);
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

And shown below, the phone_book INDEX:

mysql> SHOW INDEX FROM names\G;
*************************** 1. row ***************************
Table: names
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: name_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: names
Non_unique: 1
Key_name: phone_book
Seq_in_index: 1
Column_name: phone_num
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

But does everything carry over when using a CREATE TABLE SELECT statement?
Let’s find out.
First, I CREATE another table named few_names from the names table, with accompanying data:

mysql> CREATE TABLE few_names
-> SELECT * FROM names;
Query OK, 3 rows affected (0.27 sec)
Records: 3 Duplicates: 0 Warnings: 0

Now a check for any present INDEX(‘s) on the few_names table,

mysql> SHOW INDEX FROM few_names\G;
Empty set (0.00 sec)

No indexes are found for this table. Therefore, keep in mind that indexes do not carry over in a CREATE TABLE SELECT statement.
Note: Should you want or need the same indexes from the source table, simply prefix them in the CREATE TABLE SELECT command, prior to the SELECT.


To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.


Not All Columns

But what if you don’t want all of the columns from the source table in your new table?
Is that doable?
Let’s see…

mysql> CREATE TABLE best_friends
-> (friend_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> f_first_name VARCHAR(30) DEFAULT NULL,
-> f_last_name VARCHAR(30) DEFAULT NULL)
-> SELECT friend_id, f_first_name, f_last_name
-> FROM friends;
Query OK, 30 rows affected (0.33 sec)
Records: 30 Duplicates: 0 Warnings: 0

Looks as if that worked as expected. For brevity’s sake, I’ll retrieve just 5 records from the best_friends table and see.

mysql> SELECT *
    -> FROM best_friends
    -> LIMIT 5;
+-----------+--------------+-------------+
| friend_id | f_first_name | f_last_name |
+-----------+--------------+-------------+
|         1 | John         | Tom         |
|         2 | John         | Roberts     |
|         3 | Billy        | Bogus       |
|         4 | Jennifer     | Timie       |
|         5 | Mark         | Harmon      |
+-----------+--------------+-------------+
5 rows in set (0.00 sec)

Must I Specify?

Do you have to specify the column names and definitions in the CREATE TABLE portion of the syntax?
To know, I’ll create another table without specifying any column names or definitions this go around.

mysql> CREATE TABLE better_friends
-> SELECT friend_id, f_first_name
-> FROM friends;
Query OK, 30 rows affected (0.30 sec)
Records: 30 Duplicates: 0 Warnings: 0

That worked.
And a data check,

mysql> SELECT *
    -> FROM better_friends
    -> LIMIT 5;
+-----------+--------------+
| friend_id | f_first_name |
+-----------+--------------+
|         1 | John         |
|         2 | John         |
|         3 | Billy        |
|         4 | Jennifer     |
|         5 | Mark         |
+-----------+--------------+
5 rows in set (0.00 sec)

All is well there…


Static Tables

Let’s try something for more clarity and to understand the CREATE TABLE SELECT even deeper.
A simple insert on the names table below:

mysql> INSERT INTO names(first_name,age,phone_num)
-> VALUES('Timmy', 58, '510-142-6745');
Query OK, 1 row affected (0.03 sec)

Yields 4 records present in the table,

mysql> SELECT COUNT(*) FROM names;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.04 sec)

Since this table was the source or parent table for the newly created few_names table, did that child table (few_names) receive the newly inserted record as well?
Have a look:

mysql> SELECT COUNT(*) FROM few_names;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

Hmmm…
Definitely worth noting that this is a static table meaning operations on the parent or source table, are not performed on the child table.


CREATE TABLE SELECT Takeaways

  • Does not automatically create any indexes on the new table.
  • The new table is static.
  • Columns in the SELECT statement can be overridden by naming and specifying them in the CREATE portion.

Final Thoughts

Try CREATE TABLE SELECT when you need to quickly create a table from the structure of an existing table, with accompanying data.
Check out the official MySQL 5.7 Online Manual for more information.


A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.


Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Advertisements

One thought on “MySQL CREATE TABLE SELECT

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.