How to store MySQL query results in another Table?

sql

database-design

query-performance

data-integrity

byAnton Shumikhin·Oct 23, 2024

You can generate a CREATE TABLE ... AS SELECT query to clone data and structure into a brand-new table:

-- "Behold my creation!" - Probably you, after running this
CREATE TABLE new_table AS
SELECT * FROM existing_table WHERE condition;

or employ INSERT INTO ... SELECT to append data to a pre-existing table:

-- "Let's inject some energy here!" - Also you, probably
INSERT INTO target_table
SELECT * FROM source_table WHERE condition;

Remember: CREATE TABLE for mint condition tables, INSERT for augmenting existing ones.

Dynamically creating tables

When building a table on the fly, you're ensuring the structure pops into existence when you need it, cutting out the manual work. Ideal for dealing with those tricky queries or structure changes that keep you up at night:

-- "Just a flick of the wrist, voila!" - Your favorite SQL magician, probably
CREATE TABLE IF NOT EXISTS new_table AS
SELECT * FROM existing_table WHERE your_conditions;

IF NOT EXISTS basically tells MySQL, "Hey, if the table's not there, make it. If it is, chill out and carry on."

Speeding up with Indexing

Indexes are like speed boats for our query performance. Add an index to those frequently referenced columns and watch MySQL skim your data like a pro:

-- "Need for speed? Bring me an index!" - Probably MySQL, revving engine
CREATE INDEX idx_column ON new_table (column);

Performance gains? Checked!

Ensuring nothing breaks with data integrity

Watch out for those sneaky data conflicts and constraints; unique keys (not the ones to your heart), foreign keys (also not what you think), and their ilk. Use ON DUPLICATE KEY UPDATE to maintain your sanity and data integrity:

-- "Double trouble? Not on my watch!" - Your SQL statement, probably
INSERT INTO target_table
SELECT * FROM source_table ON DUPLICATE KEY UPDATE column = VALUES(column);

Dealing with data transfer like a pro

Aliasing for readability with "AS"

SQL benefits quite a bit from aliases when dealing with convoluted joins or subqueries. It essentially gives you ClarityInYourQuery.SQL:

-- "I'm not a col1. I shall henceforth be known as new_col1!" - SQL column aliases, probably
INSERT INTO target_table (col1, col2)
SELECT t1.col1 AS new_col1, t2.col2 AS new_col2
FROM source_table1 t1
JOIN source_table2 t2 ON t1.Id = t2.RefId
WHERE t1.condition;

Always proofread, then execute

Playtest the query in a sandbox (read: development environment) before letting it loose in the wild (read: production database). You know, just to make sure this doesn't happen:

-- "So, I may have dropped the production database." - Not you, ever

explain-codes / Sql / How to store MySQL query results in another Table?

Linked

Mysql too many indexes?



Is it better to create an index before filling a table with data, or after the data is in place?



Fastest way to copy a table in mysql?



Column calculated from another column?



Mysql very slow for alter table query



Do I need to create indexes on foreign keys on Oracle?



Fastest check if row exists in PostgreSQL



Dynamically creating tables Speeding up with Indexing Ensuring nothing breaks with data integrity Dealing with data transfer like a pro