SQL SELECT INTO Statement Explained with Examples | DbSchema
SELECT INTO copies the result of a query into a brand-new table, in one statement. SQL Server and PostgreSQL run it as written. MySQL, Oracle and SQLite create a table from a query with CREATE TABLE AS SELECT instead. To load rows into a table that already exists, use INSERT INTO SELECT.
What the SQL SELECT INTO statement does
The SELECT INTO statement is used to copy data from one table and insert it into another new table. This can be especially useful when backing up data, creating a copy for testing, or transferring data between databases.
Syntax and which engines support it
SELECT column1, column2, ...
INTO newtable
FROM oldtable;
Support splits three ways, and the middle case is the one that catches people out. SQL Server and PostgreSQL create a table from SELECT INTO. MySQL and Oracle both have a SELECT INTO, but theirs reads values into variables rather than building a table, so running the SQL Server form against them fails with an error instead of doing nothing. SQLite has no SELECT INTO at all.
| Engine | Does SELECT INTO create a table? | Create a table from a query with |
|---|---|---|
| SQL Server | Yes. SELECT ... INTO new_table creates the table in the default filegroup and inserts the rows the query returns. | SELECT ... INTO |
| PostgreSQL | Yes, though PostgreSQL's own documentation recommends CREATE TABLE AS: SELECT INTO is unavailable in ECPG and PL/pgSQL, which read INTO differently. | CREATE TABLE ... AS SELECT |
| MySQL | No. SELECT ... INTO stores column values in variables, or writes rows to a file with INTO OUTFILE or INTO DUMPFILE. | CREATE TABLE ... SELECT |
| Oracle Database | No. SELECT INTO is a PL/SQL statement that retrieves values and stores them in variables. | CREATE TABLE ... AS SELECT |
| SQLite | No. | CREATE TABLE ... AS SELECT |
Example: copy an entire table into a new one
Consider the following Students table:
| ID | Name | Age |
|---|---|---|
| 1 | Alice | 20 |
| 2 | Bob | 22 |
| 3 | Charlie | 21 |
To create a new table Backup_Students with the same data:
SELECT *
INTO Backup_Students
FROM Students;
A new table named Backup_Students is created with the same columns and data as the Students table.
SELECT INTO vs SELECT vs INSERT INTO SELECT
| Feature | SQL SELECT | SQL SELECT INTO |
|---|---|---|
| Purpose | Retrieves data from a table | Copies data from one table to a new table |
| New table creation | Doesn't create a new table | Creates a new table |
| Data modification | Doesn't modify data | Doesn't modify original data, but copies it |
| Syntax | SELECT column1, column2 FROM table; | SELECT column1, column2 INTO newtable FROM table; |
SELECT INTO creates a brand new table, so it fails the moment the target already exists. To copy rows from a query into a table that is already there, use the INSERT INTO SELECT statement; to add individual rows by hand, use the plain INSERT INTO statement.
Example: SELECT INTO from multiple joined tables
Sometimes you need to create a new table by extracting data from several existing tables at once. Any join the engine accepts works inside SELECT INTO.
Consider two tables, Students and Courses:
Students:
| ID | Name |
|---|---|
| 1 | Alice |
| 2 | Bob |
Courses:
| ID | CourseName |
|---|---|
| 1 | Math |
| 2 | History |
To combine data from both tables into a new table StudentCourses:
SELECT Students.ID, Students.Name, Courses.CourseName
INTO StudentCourses
FROM Students
INNER JOIN Courses ON Students.ID = Courses.ID;
StudentCourses table:
| ID | Name | CourseName |
|---|---|---|
| 1 | Alice | Math |
| 2 | Bob | History |
In the StudentCourses table, data from both the Students and Courses tables is combined on the matching ID.
Example: SELECT INTO with a WHERE condition
A WHERE clause filters which rows get copied, so the new table holds a subset rather than the whole source.
Using the earlier Students table, suppose we want a new table containing only students older than 21:
SELECT *
INTO Older_Students
FROM Students
WHERE Age > 21;
Older_Students table:
| ID | Name | Age |
|---|---|---|
| 2 | Bob | 22 |
Only Bob, who is older than 21, is copied to the Older_Students table.
Validate SELECT INTO results in DbSchema
Run the source SELECT on its own in the DbSchema SQL editor first, check the row count and the column list, then add the INTO clause. The SQL editor is in the free Community Edition. Browsing the copied table next to its source, one pane per table, needs the Relational Data Editor in Pro.
Common mistakes and how to avoid them
- Overwriting data: the destination table must not already exist. If it does, SELECT INTO fails.
- No filtering: SELECT INTO without a WHERE clause copies every row. Copy only what you need.
- Column mismatch: the columns in the SELECT must match the destination in both order and data type.
- Missing constraints: the new table carries columns and data, not keys, indexes or constraints. In SQLite the documentation is explicit that a table created with CREATE TABLE AS has no primary key and no constraints of any kind[5].
Practice questions
- Write a SELECT INTO query to copy all students with the name "Alice" into a new table.
- Create a new table combining data from tables Teachers and Subjects, where both tables share a TeacherID.
- Using the Orders table, create a backup table for all orders placed in the year 2022.
Frequently asked questions
Can I use SELECT INTO to copy data into an existing table?
No. SELECT INTO creates a new table and fails if the name is taken. Use INSERT INTO SELECT to load a query result into a table that already exists.
Does SELECT INTO copy constraints like primary keys and indexes?
No. It copies column definitions and data. Primary keys, foreign keys, indexes and checks have to be added to the new table afterwards.
What is the SELECT INTO equivalent in MySQL and Oracle?
CREATE TABLE ... SELECT in MySQL and CREATE TABLE ... AS SELECT in Oracle. Both engines do have a SELECT INTO, but it reads values into variables, so the SQL Server form returns an error rather than a table.
Conclusion
SELECT INTO is the shortest way to snapshot a query result into a new table on SQL Server and PostgreSQL, and CREATE TABLE AS SELECT is its equivalent everywhere else. Check the target name is free, filter with WHERE, and re-add the keys and indexes the copy does not inherit.
To try these statements against your own schema, download DbSchema and run them in the SQL editor, which is part of the free Community Edition; the Relational Data Editor for browsing the copied rows alongside the source is in Pro.
Sources
Run your SELECT INTO against a real schema
DbSchema reverse-engineers your database into an interactive diagram and runs SQL against it. The SQL editor is in the free Community Edition.
