The SQL SELECT INTO statement is a Sybase extension that can be used to insert the results of a query into a table (or a variable, depending on the DBMS).
In DBMSs such as SQL Server and PostgreSQL, the SELECT INTO statement creates a new table and inserts the resulting rows from the query into it.
In MariaDB it inserts the result set into a variable. In Oracle, it assigns the selected values to variables or collections.
MySQL and SQLite don’t support the SELECT INTO statement at all.
The examples in this article insert the result sets into a table. In MariaDB and Oracle, the destination table can be replaced by a variable name (or the collection name if you’re using Oracle).
Basic Example
Here’s a basic example to demonstrate selecting and inserting the data into a new table.
SELECT * INTO Pets2
FROM Pets;This example creates a table called Pets2 with the same definition of the table called Pets and inserts all data from Pets into Pets2.
We can verify this by selecting the contents of both tables.
SELECT * FROM Pets;
SELECT * FROM Pets2;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+ (8 rows affected) +---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+ (8 rows affected)
When the Table Already Exists
If we try to run the SELECT INTO statement again, we get an error, due to the table already existing.
SELECT * INTO Pets2
FROM Pets;Result:
Msg 2714, Level 16, State 6, Line 1 There is already an object named 'Pets2' in the database.
If you want to insert data into a table that already exists, use the INSERT INTO... SELECT statement. This will append the data to any existing data. That is, it will add new rows to the table, while keeping any existing rows
Filtering the Results
The SELECT statement can do the usual SELECT statement stuff, such as filtering the results with a WHERE clause.
SELECT * INTO Pets3
FROM Pets
WHERE DOB < '2020-06-01';In this example, I filter the data to just those pets who have a date of birth (DOB) from before the 1st of June, 2020.
Selecting from Multiple Tables
You can select data from multiple tables, then have the destination table’s definition be based on the result set.
SELECT
p.PetId,
p.PetName,
p.DOB,
pt.PetTypeId,
pt.PetType,
o.OwnerId,
o.FirstName,
o.LastName,
o.Phone,
o.Email
INTO PetsTypesOwners
FROM Pets p
INNER JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId
INNER JOIN Owners o
ON p.OwnerId = o.OwnerId;Here, we query three tables and insert the results into a table called PetsTypesOwners.
Note that I listed out each column here because I didn’t want to include all columns.
Specifically, I didn’t want to double up on the foreign key/primary key columns. In my case, the foreign keys share the same names as their primary key counterparts in the parent table, and I would have received an error due to duplicate column names being created in the destination table.
Here’s what I mean.
SELECT *
INTO PetsTypesOwners2
FROM Pets p
INNER JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId
INNER JOIN Owners o
ON p.OwnerId = o.OwnerId;Result:
Msg 2705, Level 16, State 3, Line 1 Column names in each table must be unique. Column name 'PetTypeId' in table 'PetsTypesOwners2' is specified more than once.
If your foreign keys use different column names to the primary keys, then you’d probably end up with a destination table that contains unnecessary columns (one for the primary key, one for the foreign key, and each containing the same values).
If you really want to include such duplicate columns, but they share the same name, you can always use aliases to assign them with a different name in the destination table.
SELECT
p.PetId,
p.OwnerId AS PetOwnerId,
p.PetTypeId AS PetPetTypeId,
p.PetName,
p.DOB,
pt.PetTypeId,
pt.PetType,
o.OwnerId,
o.FirstName,
o.LastName,
o.Phone,
o.Email
INTO PetsTypesOwners3
FROM Pets p
INNER JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId
INNER JOIN Owners o
ON p.OwnerId = o.OwnerId;In this case I used column aliases to reassign the name of two columns to PetOwnerId and PetPetTypeId.
SELECT INTO From a View
You can also select data from a view if required.
SELECT * INTO PetTypeCount
FROM vPetTypeCount;This selects data from the vPetTypeCount view and inserts it into a new table called PetTypeCount.
We can verify this with a SELECT statement.
SELECT * FROM vPetTypeCount;
SELECT * FROM PetTypeCount;Result:
+-----------+---------+ | PetType | Count | |-----------+---------| | Bird | 1 | | Cat | 3 | | Dog | 4 | +-----------+---------+ (3 rows affected) +-----------+---------+ | PetType | Count | |-----------+---------| | Bird | 1 | | Cat | 3 | | Dog | 4 | +-----------+---------+ (3 rows affected)
DBMS Support
As mentioned, the SELECT INTO statement is a Sybase extension, and it’s not supported by all major DBMSs. For example, MySQL and SQLite don’t support it.
Also, out of the DBMSs that do support it, the actual implementation varies somewhat between DBMS. The above examples were done in SQL Server. In MariaDB and Oracle you can replace the destination table with a variable name (or collection name in Oracle).
If your DBMS doesn’t support the SELECT INTO statement, chances are it does support the INSERT INTO... SELECT statement, so you should try that instead.