How to Insert Rows Using SELECT in SQL?
Inserting data from one table to another can streamline data management and enhance efficiency. This guide explains how to use the INSERT INTO ... SELECT statement.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Insert rows into a table by selecting from another table using INSERT INTO ... SELECT"
INSERT INTO target_table (column1, column2) SELECT column1, column2 FROM source_table;This SQL statement inserts rows into a target table by selecting data from a source table.
Step-by-step breakdown
- Specify the target table where data will be inserted.
- List the columns in the target table that will receive the data.
- Use the SELECT statement to retrieve data from the source table.
- Ensure the selected columns match the target columns in data type and order.
Edge cases & warnings
- Inserting data when the source table is empty results in no new rows.
- Data type mismatches between source and target columns can cause errors.
- Inserting duplicate rows if the target table has unique constraints.
- Selecting from a view instead of a table may lead to unexpected results.
Examples
"INSERT INTO employees (name, age) SELECT name, age FROM new_hires;"
Rows from new_hires are inserted into employees."INSERT INTO orders (product_id, quantity) SELECT product_id, quantity FROM pending_orders;"
Rows from pending_orders are inserted into orders.Frequently asked questions
Can I insert data into multiple tables at once?
No, you can only insert into one table at a time using this method.
What happens if the source table has no matching rows?
No rows will be inserted into the target table.
Can I use JOIN in the SELECT statement?
Yes, you can join multiple tables in the SELECT statement to insert combined data.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.