INSERT INTO SELECT
In this page:
INSERT INTO target_table (column1, column2)
SELECT column1, column2 FROM source_table
[WHERE condition];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);- Selecting more or fewer columns than the target table expects, so the counts do not match and the insert fails.
- Running it twice and inserting the same rows again, when the target has no unique key.
- Getting the column order wrong when the target table has a different column order than the source.
INSERT INTOadds one row or multiple rows to a table.SELECTretrieves data,DISTINCTremoves duplicates, and aliases rename columns or tables in results.INSERT INTO SELECTcopies rows from one query result into a table.
Chapter Quiz — Complete all 6 topics to unlock
0/6 topics done
Complete these topics first: