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:

We Want Data!

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

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

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.

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.

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

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.

And shown below, the phone_book INDEX:

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:

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

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…

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

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.

That worked.
And a data check,

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:

Yields 4 records present in the table,

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:

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.