SELECT INTO & INSERT INTO SELECT

SELECT INTO creates a new table from a query result. SQL Server and Access support it directly. MySQL/PostgreSQL use CREATE TABLE ... AS SELECT instead.

SELECT INTO — copy query results to a new table

SELECT INTO creates a new table from a query result. SQL Server and Access support it directly. MySQL/PostgreSQL use CREATE TABLE ... AS SELECT instead.

Real-life example: SELECT INTO is photocopying the honor roll onto a fresh sheet titled 'Top Students March'.

SQL Server style SELECT INTO

-- SQL Server / Access
SELECT student_id, full_name, grade_level
INTO top_students
FROM students
WHERE grade_level = 12;

MySQL / PostgreSQL equivalent

CREATE TABLE top_students AS
SELECT student_id, full_name, grade_level
FROM students
WHERE grade_level = 12;

INSERT INTO SELECT — copy into existing table

INSERT INTO ... SELECT adds rows from a query into a table that already exists. Column types must match.

Real-life example: INSERT INTO SELECT is adding this month's new enrollments into the master archive table.

Archive high scorers into a backup table

INSERT INTO honor_roll (student_id, course_id, score)
SELECT student_id, course_id, score
FROM enrollments
WHERE score >= 90;