SQL SELECT INTO

Learn how SELECT INTO is used to copy query results into a new table and how to achieve the same result in SQLite.

What is SELECT INTO?

The SELECT INTO statement is traditionally used to copy data from one table into a new table. It can copy all columns or only selected columns from an existing table.

Important: SQLite does not support the SELECT INTO syntax. In SQLite, the equivalent operation is performed using CREATE TABLE ... AS SELECT. The examples below use this SQLite-compatible syntax so that every Test My Code example can run correctly.

Basic SELECT INTO Syntax

In database systems that support SELECT INTO, the basic syntax is:

SELECT column1, column2
INTO new_table
FROM existing_table;

The SQLite equivalent is:

CREATE TABLE new_table AS
SELECT column1, column2
FROM existing_table;

Copy an Entire Table

You can copy all columns and rows from an existing table into a new table by using CREATE TABLE AS SELECT.

DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS students_backup;

CREATE TABLE students (
  student_id INTEGER,
  student_name TEXT,
  course TEXT,
  score INTEGER
);

INSERT INTO students VALUES
  (1, 'Aarav', 'SQL', 88),
  (2, 'Meera', 'Python', 92),
  (3, 'Kabir', 'HTML', 79);

CREATE TABLE students_backup AS
SELECT *
FROM students;

SELECT *
FROM students_backup
ORDER BY student_id;

Copy Selected Columns

SELECT INTO does not require you to copy every column. You can select only the columns you need when creating the new table.

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS product_names;

CREATE TABLE products (
  product_id INTEGER,
  product_name TEXT,
  category TEXT,
  price INTEGER
);

INSERT INTO products VALUES
  (1, 'Mechanical Keyboard', 'Accessories', 2800),
  (2, 'Wireless Mouse', 'Accessories', 1200),
  (3, 'USB Microphone', 'Audio', 3500);

CREATE TABLE product_names AS
SELECT product_id, product_name
FROM products;

SELECT *
FROM product_names
ORDER BY product_id;

Copy Filtered Data

A WHERE clause can be used to create a new table containing only rows that satisfy a particular condition.

DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS high_paid_employees;

CREATE TABLE employees (
  employee_id INTEGER,
  employee_name TEXT,
  department TEXT,
  salary INTEGER
);

INSERT INTO employees VALUES
  (1, 'Riya', 'Design', 58000),
  (2, 'Arjun', 'Engineering', 82000),
  (3, 'Tara', 'Marketing', 64000),
  (4, 'Vikram', 'Engineering', 95000);

CREATE TABLE high_paid_employees AS
SELECT employee_id, employee_name, department, salary
FROM employees
WHERE salary >= 80000;

SELECT *
FROM high_paid_employees
ORDER BY salary DESC;

Copy Sorted Data

SELECT INTO can be combined with a query containing ORDER BY. In SQLite, the same query can be used with CREATE TABLE AS SELECT.

DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS expensive_books;

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  price INTEGER
);

INSERT INTO books VALUES
  (1, 'SQL Fundamentals', 700),
  (2, 'Database Design', 1200),
  (3, 'Web Development', 900),
  (4, 'Data Analysis', 1500);

CREATE TABLE expensive_books AS
SELECT book_id, title, price
FROM books
WHERE price >= 900
ORDER BY price DESC;

SELECT *
FROM expensive_books;

Copy Data with Calculated Columns

You can create a new table containing calculated values from the original table.

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS product_summary;

CREATE TABLE products (
  product_id INTEGER,
  product_name TEXT,
  price INTEGER,
  quantity INTEGER
);

INSERT INTO products VALUES
  (1, 'Keyboard', 1800, 4),
  (2, 'Monitor', 12500, 2),
  (3, 'Webcam', 3200, 3);

CREATE TABLE product_summary AS
SELECT
  product_id,
  product_name,
  price,
  quantity,
  price * quantity AS total_value
FROM products;

SELECT *
FROM product_summary
ORDER BY total_value DESC;

Copy Grouped Results

SELECT INTO can also be used conceptually to store the result of an aggregate query in a new table.

DROP TABLE IF EXISTS sales;
DROP TABLE IF EXISTS sales_summary;

CREATE TABLE sales (
  sale_id INTEGER,
  category TEXT,
  amount INTEGER
);

INSERT INTO sales VALUES
  (1, 'Books', 700),
  (2, 'Books', 900),
  (3, 'Games', 1200),
  (4, 'Games', 800),
  (5, 'Accessories', 500);

CREATE TABLE sales_summary AS
SELECT
  category,
  SUM(amount) AS total_sales,
  COUNT(*) AS order_count
FROM sales
GROUP BY category;

SELECT *
FROM sales_summary
ORDER BY total_sales DESC;

Copy Data from Multiple Tables

A SELECT query can use JOIN operations before creating the new table. This allows data from multiple tables to be stored in one new table.

DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customer_orders;

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

CREATE TABLE orders (
  order_id INTEGER,
  customer_id INTEGER,
  amount INTEGER
);

INSERT INTO customers VALUES
  (1, 'Anaya'),
  (2, 'Rohan'),
  (3, 'Mira');

INSERT INTO orders VALUES
  (101, 1, 1500),
  (102, 2, 900),
  (103, 3, 2100);

CREATE TABLE customer_orders AS
SELECT
  c.customer_name,
  o.order_id,
  o.amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id;

SELECT *
FROM customer_orders
ORDER BY order_id;

Create a Table from DISTINCT Values

DISTINCT can be used to create a new table containing only unique values.

DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE employees (
  employee_id INTEGER,
  employee_name TEXT,
  department TEXT
);

INSERT INTO employees VALUES
  (1, 'Aarav', 'Engineering'),
  (2, 'Meera', 'Design'),
  (3, 'Kabir', 'Engineering'),
  (4, 'Riya', 'Marketing'),
  (5, 'Dev', 'Design');

CREATE TABLE departments AS
SELECT DISTINCT department
FROM employees;

SELECT *
FROM departments
ORDER BY department;

SELECT INTO vs INSERT INTO … SELECT

SELECT INTO creates a new table from a query result. If the destination table already exists, INSERT INTO ... SELECT is generally used to copy rows into it.

Statement	Purpose	SQLite
SELECT INTO	Creates a new table from a query	Not supported
CREATE TABLE AS SELECT	Creates a new table from a query	Supported
INSERT INTO … SELECT	Copies rows into an existing table	Supported

SELECT INTO vs CREATE TABLE AS SELECT

The following example shows the conceptual difference between the traditional SELECT INTO syntax and the SQLite-compatible syntax.

Traditional SQL	SQLite
SELECT * INTO backup FROM employees;	CREATE TABLE backup AS SELECT * FROM employees;
SELECT name, salary INTO high_paid FROM employees WHERE salary > 70000;	CREATE TABLE high_paid AS SELECT name, salary FROM employees WHERE salary > 70000;

Advantages of SELECT INTO

Can quickly create a new table from query results.
Can copy an entire table or selected columns.
Can copy filtered data using WHERE.
Can create summary tables using aggregate functions.
Can combine data from multiple tables using JOIN.

Best Practices

Remember that SQLite uses CREATE TABLE ... AS SELECT instead of SELECT INTO.
Use DROP TABLE IF EXISTS in repeatable tutorial examples.
Select only the columns required for the new table.
Use WHERE when you only need a subset of the source data.
Use INSERT INTO … SELECT when the destination table already exists.
Remember that CREATE TABLE AS SELECT creates the table structure from the query result but does not copy indexes or constraints from the original table.

The SELECT INTO statement is commonly used to create a new table from the results of a SELECT query. Because SQLite does not support SELECT INTO directly, use CREATE TABLE ... AS SELECT for the same practical purpose.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.