Select INTO in MySQL
To clone both structure and data of a table in MySQL, replace SELECT INTO with CREATE TABLE ... AS SELECT:
**``` CREATE TABLE new_table AS SELECT * FROM existing_table;
This quickly constructs **`new_table`** identical to **`existing_table`** in terms of schema and contents.
For creating a custom structure or including specific data subsets, modify the `SELECT` clause appropriately.
The CREATE TABLE AS SELECT Gambit
Here is the secret sauce for replicating a table or its contents in MySQL: CREATE TABLE ... AS SELECT. It not only mimics, but dynamically creates a new table based on result-set from a given SELECT statement.
Mind the Gap: Data Types & Constraints
Before leaping, ensure your source's data types align with the new table's. Constraints, indexes, and default values, unfortunately, stay behind—manual intervention required post-query execution.
Extracting Specific Data
Need a subset? The WHERE clause or specific columns selection in SELECT statement is your friend:
**``` CREATE TABLE new_table AS SELECT column1, column2 FROM existing_table WHERE condition;
### "Undeclared variable" Error? Not on Our Watch!
Using `SELECT INTO` directly in MySQL? Expect an "Undeclared Variable" error. MySQL does not support `SELECT INTO` syntax to create new tables a la MSSQL. But we have solutions.
### Pre-existing Table: Load Data with INSERT INTO SELECT
To add data from another table into an existing one, use `INSERT INTO ... SELECT`:
**```
INSERT INTO target_table (column1, column2) SELECT column1, column2 FROM source_table WHERE condition;
```**
Remember to match column order and count in `INSERT INTO` and `SELECT` parts.
## Temporary Tables: Your Test Kitchen!
Dealing with **complex operations**? Temporary tables act as **sandbox**: perform all tweaks before final execution:
`CREATE TEMPORARY TABLE temp_table AS SELECT * FROM existing_table; -- no commitments here, it's just a TEMP table! 😁`
Do all modifications, then reflect results to a **permanent table**.
## Big Data? Performance Comes First!
When juggling large datasets, remember, performance is key. Indexes aren't part of the cloning process, so post-execution indexing is a must. Ever thought of being a detective? Use `EXPLAIN` to inspect query performance and **optimize** accordingly.
## Complex Column Mapping: Not So Complex!
Straight copying - too vanilla? When dealing with **complex schemas** or necessary **data gymnastics** before insertion, define **aliases** and **functions** within the `SELECT` statement:
**```
CREATE TABLE new_table AS SELECT
source_column1 AS no_more_source, -- 🕵️♀️ undercover aliasing
CONCAT(source_column2, ' ', source_column3) AS two_for_one -- 🤝 collaboration
FROM existing_table;
```**
Customized schema and data formats for new table? Piece of cake.
Was this article helpful?