INSERT INTO SELECT
In this page:
Copying Data Between Tables
INSERT INTO ... SELECT copies rows directly from one table into another in a single statement, which is far more efficient than reading rows out into an application and inserting them back one at a time.
Example: Copying Data Between Tables
INSERT INTO archived_users SELECT * FROM users;Copying Specific Columns
You don't have to copy every column — selecting only the specific columns you need still works, as long as their data types line up correctly with the destination table's matching columns.
Example: Copying Specific Columns
INSERT INTO archived_users (name, email) SELECT name, email FROM users;Copying with Conditions
Adding a WHERE clause to the SELECT portion lets you copy only a filtered subset of rows, rather than duplicating an entire source table into the destination.
Example: Copying with Conditions
INSERT INTO archived_users SELECT * FROM users WHERE is_active = 0;Copying with Constant Values
You can mix real column values from the source table with hardcoded constant values in the same SELECT list, which is a common way to tag newly copied rows with something like a migrated status flag.
Example: Copying with Constant Values
INSERT INTO archived_users (name, archived_by) SELECT name, 'admin' FROM users;Preventing Duplicate Entries
Running the same INSERT INTO ... SELECT twice will duplicate the data a second time unless you add a WHERE clause or constraint that specifically prevents re-copying rows already present.
Example: Preventing Duplicate Entries
INSERT INTO archived_users
SELECT * FROM users
WHERE id NOT IN (SELECT id FROM archived_users);Chapter Quiz — Complete all 6 topics to unlock
0/6 topics done
Complete these topics first: