SQL INSERT SELECT

Add W3Buddy as a preferred source on Google

Article Summary

INSERT SELECT is used to copy data from one table into another. This is very useful for data migration, backups, or transformations. 🔹 Basic Syntax 🔹 Example 🔹 Insert All Columns (If Structure Matches) 🔹 Oracle Notes 🧠 Quick Recap Use Case Example Syntax Copy selected rows INSERT INTO target SELECT ... FROM source Insert […]

INSERT SELECT is used to copy data from one table into another. This is very useful for data migration, backups, or transformations.

🔹 Basic Syntax

INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM source_table
WHERE condition;

🔹 Example

-- Copy employees from 'IT' department into backup table
INSERT INTO employees_backup (id, name, department)
SELECT employee_id, name, department
FROM employees
WHERE department = 'IT';

🔹 Insert All Columns (If Structure Matches)

INSERT INTO employees_archive
SELECT * FROM employees
WHERE status = 'inactive';

🔹 Oracle Notes

Works the same way in Oracle.
You can also use INSERT /*+ APPEND */ for performance tuning in large loads.

🧠 Quick Recap

Use Case	Example Syntax
Copy selected rows	`INSERT INTO target SELECT ... FROM source`
Insert all columns	`INSERT INTO target SELECT * FROM source`
With condition/filter	Add `WHERE` clause to the `SELECT` part

✅ Ideal for copying, archiving, or transforming data between tables