SQL · Theory

Insert Into Select

Theory

39/439

Insert Into Select

INSERT INTO archive_students (roll, name, marks) SELECT roll, name, marks FROM students WHERE marks < 40; copies query results into another table. Column count and types must line up.

This is how you move a subset without typing VALUES. CREATE TABLE archive AS SELECT … also works if the table does not exist (watch indexes).

Trap — INSERT SELECT without WHERE on a huge table — lock and disk spike.

On the example next to this theory — Insert Into Select — iNSERT adds rows. List columns explicitly for clarity.

Diagram

  FROM students
        │
        ▼
  WHERE city = 'Pune'
        │
        ▼
  SELECT name, marks

Exam tip

INSERT INTO archive SELECT … FROM students WHERE marks < 40;

Insert Into Select — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products (id, name, price) VALUES
  (1, 'Mouse', 499),
  (2, 'Keyboard', 999);
SELECT * FROM products;

Insert Into Select — iNSERT adds rows. List columns explicitly for clarity.

Short notes

DefINSERT INTO t SELECT … copies query output into t.
RuleColumns must match. Filter with WHERE.
TrapUnfiltered SELECT on a large table.

SQL · Theory

Insert Into Select

← All stacks

Theory

39/439

Insert Into Select

INSERT INTO archive_students (roll, name, marks) SELECT roll, name, marks FROM students WHERE marks < 40; copies query results into another table. Column count and types must line up.

This is how you move a subset without typing VALUES. CREATE TABLE archive AS SELECT … also works if the table does not exist (watch indexes).

Trap — INSERT SELECT without WHERE on a huge table — lock and disk spike.

On the example next to this theory — Insert Into Select — iNSERT adds rows. List columns explicitly for clarity.

Diagram

  FROM students
        │
        ▼
  WHERE city = 'Pune'
        │
        ▼
  SELECT name, marks

Exam tip

INSERT INTO archive SELECT … FROM students WHERE marks < 40;

Insert Into Select — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products (id, name, price) VALUES
  (1, 'Mouse', 499),
  (2, 'Keyboard', 999);
SELECT * FROM products;

Insert Into Select — iNSERT adds rows. List columns explicitly for clarity.

Short notes

DefINSERT INTO t SELECT … copies query output into t.
RuleColumns must match. Filter with WHERE.
TrapUnfiltered SELECT on a large table.

Insert Into Select

Insert Into Select — sample query

Short notes

Questions

Insert Into Select

Insert Into Select — sample query

Short notes

Questions