What is INSERT INTO SELECT?
The INSERT INTO SELECT statement is used to copy data from
one table into another existing table. Unlike SELECT INTO,
the destination table must already exist before the data is inserted.
It is especially useful when you need to transfer selected rows, selected columns, filtered records, or query results into another table. The examples below are written to work directly with SQLite.
Basic INSERT INTO SELECT Syntax
The basic syntax copies selected columns from one table into an existing destination table.
INSERT INTO destination_table (column1, column2)
SELECT column1, column2
FROM source_table;Copy All Columns
If both tables have matching columns, you can copy all columns from the source table into the destination table.
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS course_archive;
CREATE TABLE courses (
course_id INTEGER,
course_name TEXT,
duration INTEGER
);
CREATE TABLE course_archive (
course_id INTEGER,
course_name TEXT,
duration INTEGER
);
INSERT INTO courses VALUES
(1, 'SQL Basics', 30),
(2, 'Web Design', 45),
(3, 'Data Analysis', 60);
INSERT INTO course_archive
SELECT *
FROM courses;
SELECT *
FROM course_archive
ORDER BY course_id;Copy Selected Columns
You can select only the columns that are required instead of copying every column from the source table.
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS product_list;
CREATE TABLE products (
product_id INTEGER,
product_name TEXT,
category TEXT,
price INTEGER
);
CREATE TABLE product_list (
product_id INTEGER,
product_name TEXT
);
INSERT INTO products VALUES
(1, 'Mechanical Keyboard', 'Accessories', 2800),
(2, 'Studio Headphones', 'Audio', 4200),
(3, 'USB Hub', 'Accessories', 1500);
INSERT INTO product_list (product_id, product_name)
SELECT product_id, product_name
FROM products;
SELECT *
FROM product_list
ORDER BY product_id;INSERT INTO SELECT with WHERE
The WHERE clause allows you to copy only rows that satisfy
a specific condition.
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS senior_employees;
CREATE TABLE employees (
employee_id INTEGER,
employee_name TEXT,
department TEXT,
salary INTEGER
);
CREATE TABLE senior_employees (
employee_id INTEGER,
employee_name TEXT,
department TEXT,
salary INTEGER
);
INSERT INTO employees VALUES
(1, 'Nisha', 'Design', 58000),
(2, 'Arjun', 'Engineering', 85000),
(3, 'Mira', 'Marketing', 67000),
(4, 'Kabir', 'Engineering', 92000);
INSERT INTO senior_employees
SELECT *
FROM employees
WHERE salary >= 80000;
SELECT *
FROM senior_employees
ORDER BY salary DESC;INSERT INTO SELECT with DISTINCT
The DISTINCT keyword can be used to insert only unique
values into the destination table.
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE employees (
employee_id INTEGER,
employee_name TEXT,
department TEXT
);
CREATE TABLE departments (
department TEXT
);
INSERT INTO employees VALUES
(1, 'Aarav', 'Engineering'),
(2, 'Meera', 'Design'),
(3, 'Rohan', 'Engineering'),
(4, 'Tara', 'Marketing'),
(5, 'Dev', 'Design');
INSERT INTO departments (department)
SELECT DISTINCT department
FROM employees;
SELECT *
FROM departments
ORDER BY department;INSERT INTO SELECT with ORDER BY
You can use ORDER BY in the SELECT query when the order of
rows being inserted is relevant to the query result.
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS selected_books;
CREATE TABLE books (
book_id INTEGER,
title TEXT,
price INTEGER
);
CREATE TABLE selected_books (
book_id INTEGER,
title TEXT,
price INTEGER
);
INSERT INTO books VALUES
(1, 'SQL Essentials', 650),
(2, 'Database Systems', 1400),
(3, 'Web Programming', 900),
(4, 'Data Science', 1800);
INSERT INTO selected_books
SELECT *
FROM books
WHERE price >= 900
ORDER BY price DESC;
SELECT *
FROM selected_books
ORDER BY price DESC;INSERT Calculated Values with SELECT
The SELECT query can calculate values before inserting them into the destination table.
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS order_summary;
CREATE TABLE orders (
order_id INTEGER,
product TEXT,
quantity INTEGER,
price INTEGER
);
CREATE TABLE order_summary (
order_id INTEGER,
product TEXT,
total_amount INTEGER
);
INSERT INTO orders VALUES
(101, 'Keyboard', 2, 1800),
(102, 'Mouse', 3, 900),
(103, 'Monitor', 1, 12500);
INSERT INTO order_summary (order_id, product, total_amount)
SELECT
order_id,
product,
quantity * price
FROM orders;
SELECT *
FROM order_summary
ORDER BY order_id;INSERT Aggregated Results
INSERT INTO SELECT can also insert the results of aggregate functions
such as SUM() and COUNT() into another table.
DROP TABLE IF EXISTS sales;
DROP TABLE IF EXISTS category_summary;
CREATE TABLE sales (
sale_id INTEGER,
category TEXT,
amount INTEGER
);
CREATE TABLE category_summary (
category TEXT,
total_sales INTEGER,
order_count INTEGER
);
INSERT INTO sales VALUES
(1, 'Books', 800),
(2, 'Books', 1200),
(3, 'Games', 1500),
(4, 'Games', 700),
(5, 'Accessories', 950);
INSERT INTO category_summary (category, total_sales, order_count)
SELECT
category,
SUM(amount),
COUNT(*)
FROM sales
GROUP BY category;
SELECT *
FROM category_summary
ORDER BY total_sales DESC;INSERT Data from Two Tables
A SELECT query can use a JOIN to combine information from multiple tables before inserting the result into the destination table.
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS purchases;
DROP TABLE IF EXISTS customer_purchases;
CREATE TABLE customers (
customer_id INTEGER,
customer_name TEXT
);
CREATE TABLE purchases (
purchase_id INTEGER,
customer_id INTEGER,
amount INTEGER
);
CREATE TABLE customer_purchases (
customer_name TEXT,
purchase_id INTEGER,
amount INTEGER
);
INSERT INTO customers VALUES
(1, 'Anaya'),
(2, 'Rohan'),
(3, 'Mira');
INSERT INTO purchases VALUES
(501, 1, 2200),
(502, 2, 1450),
(503, 3, 3200);
INSERT INTO customer_purchases (customer_name, purchase_id, amount)
SELECT
c.customer_name,
p.purchase_id,
p.amount
FROM customers AS c
INNER JOIN purchases AS p
ON c.customer_id = p.customer_id;
SELECT *
FROM customer_purchases
ORDER BY purchase_id;INSERT Filtered Data from Another Table
You can use conditions to transfer only specific records from a source table into an existing destination table.
DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS low_stock;
CREATE TABLE inventory (
item_id INTEGER,
item_name TEXT,
stock INTEGER
);
CREATE TABLE low_stock (
item_id INTEGER,
item_name TEXT,
stock INTEGER
);
INSERT INTO inventory VALUES
(1, 'USB Cable', 35),
(2, 'HDMI Cable', 8),
(3, 'Laptop Stand', 18),
(4, 'Mouse Pad', 5),
(5, 'Desk Lamp', 25);
INSERT INTO low_stock
SELECT *
FROM inventory
WHERE stock < 10;
SELECT *
FROM low_stock
ORDER BY stock;INSERT INTO SELECT vs SELECT INTO
| Feature | INSERT INTO SELECT | SELECT INTO |
|---|---|---|
| Destination table | Must already exist | Creates a new table |
| Main purpose | Copies rows into an existing table | Creates a table from query results |
| SQLite support | Yes | No |
| SQLite alternative | INSERT INTO ... SELECT | CREATE TABLE ... AS SELECT |
Advantages of INSERT INTO SELECT
- Copies data between existing tables.
- Can copy only selected columns.
- Can filter rows using WHERE.
- Can combine data from multiple tables using JOIN.
- Can insert calculated values.
- Can insert grouped and aggregated query results.
Best Practices
- Always specify destination columns when the column mapping is important.
- Make sure the number and order of selected columns match the destination columns.
- Use WHERE when you only need specific rows.
- Use JOIN carefully when copying data from multiple tables.
- Check for duplicate records before inserting data.
- Use transactions when performing important bulk insert operations.
The INSERT INTO SELECT statement is a powerful way to transfer query results into an existing table. It can copy complete rows, selected columns, filtered records, calculated values, and even results produced by JOIN and aggregate queries.
🧪 Test Your SQL Code
Edit the SQL code on the left and click "Run Code" to see the result on the right.
Click "Run Code" to see the result here.