{"id":11708,"date":"2020-11-29T11:53:11","date_gmt":"2020-11-29T01:53:11","guid":{"rendered":"https:\/\/database.guide\/?p=11708"},"modified":"2020-11-29T11:53:13","modified_gmt":"2020-11-29T01:53:13","slug":"sql-select-into-statement","status":"publish","type":"post","link":"https:\/\/database.guide\/sql-select-into-statement\/","title":{"rendered":"SQL SELECT INTO Statement"},"content":{"rendered":"\n

The SQL SELECT INTO<\/code> 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<\/a>). <\/p>\n\n\n\n\n\n\n\n

In DBMSs such as SQL Server and PostgreSQL, the SELECT INTO<\/code> statement creates a new table and inserts the resulting rows from the query into it.<\/p>\n\n\n\n

In MariaDB it inserts the result set into a variable. In Oracle, it assigns the selected values to variables or collections.<\/p>\n\n\n\n

MySQL and SQLite don’t support the SELECT INTO<\/code> statement at all.<\/p>\n\n\n\n

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).<\/p>\n\n\n\n

Basic Example<\/h2>\n\n\n\n

Here’s a basic example to demonstrate selecting and inserting the data into a new table.<\/p>\n\n\n\n

SELECT * INTO Pets2\nFROM Pets;<\/code><\/pre>\n\n\n\n
This example creates a table called Pets2<\/code> with the same definition of the table called Pets<\/code> and inserts all data from Pets<\/code> into Pets2<\/code>.<\/p>\n\n\n\n
We can verify this by selecting the contents of both tables.<\/p>\n\n\n\n
SELECT * FROM Pets;\nSELECT * FROM Pets2;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------+-------------+-----------+-----------+------------+\n| PetId \u00a0 | PetTypeId \u00a0 | OwnerId \u00a0 | PetName \u00a0 | DOB\u00a0 \u00a0 \u00a0 \u00a0 |\n|---------+-------------+-----------+-----------+------------|\n| 1 \u00a0 \u00a0 \u00a0 | 2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 | Fluffy\u00a0 \u00a0 | 2020-11-20 |\n| 2 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 | Fetch \u00a0 \u00a0 | 2019-08-16 |\n| 3 \u00a0 \u00a0 \u00a0 | 2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 2 \u00a0 \u00a0 \u00a0 \u00a0 | Scratch \u00a0 | 2018-10-01 |\n| 4 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 | Wag \u00a0 \u00a0 \u00a0 | 2020-03-15 |\n| 5 \u00a0 \u00a0 \u00a0 | 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 1 \u00a0 \u00a0 \u00a0 \u00a0 | Tweet \u00a0 \u00a0 | 2020-11-28 |\n| 6 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 4 \u00a0 \u00a0 \u00a0 \u00a0 | Fluffy\u00a0 \u00a0 | 2020-09-17 |\n| 7 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 2 \u00a0 \u00a0 \u00a0 \u00a0 | Bark\u00a0 \u00a0 \u00a0 | NULL \u00a0 \u00a0 \u00a0 |\n| 8 \u00a0 \u00a0 \u00a0 | 2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 4 \u00a0 \u00a0 \u00a0 \u00a0 | Meow\u00a0 \u00a0 \u00a0 | NULL \u00a0 \u00a0 \u00a0 |\n+---------+-------------+-----------+-----------+------------+\n(8 rows affected)\n+---------+-------------+-----------+-----------+------------+\n| PetId \u00a0 | PetTypeId \u00a0 | OwnerId \u00a0 | PetName \u00a0 | DOB\u00a0 \u00a0 \u00a0 \u00a0 |\n|---------+-------------+-----------+-----------+------------|\n| 1 \u00a0 \u00a0 \u00a0 | 2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 | Fluffy\u00a0 \u00a0 | 2020-11-20 |\n| 2 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 | Fetch \u00a0 \u00a0 | 2019-08-16 |\n| 3 \u00a0 \u00a0 \u00a0 | 2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 2 \u00a0 \u00a0 \u00a0 \u00a0 | Scratch \u00a0 | 2018-10-01 |\n| 4 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 | Wag \u00a0 \u00a0 \u00a0 | 2020-03-15 |\n| 5 \u00a0 \u00a0 \u00a0 | 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 1 \u00a0 \u00a0 \u00a0 \u00a0 | Tweet \u00a0 \u00a0 | 2020-11-28 |\n| 6 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 4 \u00a0 \u00a0 \u00a0 \u00a0 | Fluffy\u00a0 \u00a0 | 2020-09-17 |\n| 7 \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 2 \u00a0 \u00a0 \u00a0 \u00a0 | Bark\u00a0 \u00a0 \u00a0 | NULL \u00a0 \u00a0 \u00a0 |\n| 8 \u00a0 \u00a0 \u00a0 | 2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 4 \u00a0 \u00a0 \u00a0 \u00a0 | Meow\u00a0 \u00a0 \u00a0 | NULL \u00a0 \u00a0 \u00a0 |\n+---------+-------------+-----------+-----------+------------+\n(8 rows affected)<\/pre>\n\n\n\n
When the Table Already Exists<\/h2>\n\n\n\n
If we try to run the SELECT INTO<\/code> statement again, we get an error, due to the table already existing.<\/p>\n\n\n\n
SELECT * INTO Pets2\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Msg 2714, Level 16, State 6, Line 1\nThere is already an object named 'Pets2' in the database.<\/pre>\n\n\n\n
If you want to insert data into a table that already exists, use the INSERT INTO... SELECT<\/code> statement<\/a>. This will append the data to any existing data. That is, it will add new rows to the table, while keeping any existing rows<\/p>\n\n\n\n
Filtering the Results<\/h2>\n\n\n\n
The SELECT<\/code> statement can do the usual SELECT<\/code> statement stuff, such as filtering the results with a WHERE<\/code> clause.<\/p>\n\n\n\n
SELECT * INTO Pets3\nFROM Pets\nWHERE DOB < '2020-06-01';<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
Selecting from Multiple Tables<\/h2>\n\n\n\n
You can select data from multiple tables, then have the destination table’s definition be based on the result set.<\/p>\n\n\n\n
SELECT\n    p.PetId,\n    p.PetName,\n    p.DOB,\n    pt.PetTypeId,\n    pt.PetType,    \n    o.OwnerId,\n    o.FirstName,\n    o.LastName,\n    o.Phone,\n    o.Email\nINTO PetsTypesOwners\nFROM Pets p \nINNER JOIN PetTypes pt \nON p.PetTypeId = pt.PetTypeId \nINNER JOIN Owners o \nON p.OwnerId = o.OwnerId;<\/code><\/pre>\n\n\n\n
Here, we query three tables and insert the results into a table called PetsTypesOwners<\/code>.<\/p>\n\n\n\n
Note that I listed out each column here because I didn’t want to include all columns.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Here’s what I mean.<\/p>\n\n\n\n
SELECT *\nINTO PetsTypesOwners2\nFROM Pets p \nINNER JOIN PetTypes pt \nON p.PetTypeId = pt.PetTypeId \nINNER JOIN Owners o \nON p.OwnerId = o.OwnerId;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Msg 2705, Level 16, State 3, Line 1\nColumn names in each table must be unique. Column name 'PetTypeId' in table 'PetsTypesOwners2' is specified more than once.<\/pre>\n\n\n\n
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).<\/p>\n\n\n\n
If you really want to include such duplicate columns, but they share the same name, you can always use aliases<\/a> to assign them with a different name in the destination table.<\/p>\n\n\n\n
SELECT\n    p.PetId, \n    p.OwnerId AS PetOwnerId, \n    p.PetTypeId AS PetPetTypeId,\n    p.PetName,\n    p.DOB,\n    pt.PetTypeId,\n    pt.PetType,    \n    o.OwnerId,\n    o.FirstName,\n    o.LastName,\n    o.Phone,\n    o.Email\nINTO PetsTypesOwners3\nFROM Pets p \nINNER JOIN PetTypes pt \nON p.PetTypeId = pt.PetTypeId \nINNER JOIN Owners o \nON p.OwnerId = o.OwnerId;<\/code><\/pre>\n\n\n\n
In this case I used column aliases to reassign the name of two columns to PetOwnerId<\/code> and PetPetTypeId<\/code>. <\/p>\n\n\n\n
SELECT INTO From a View<\/h2>\n\n\n\n
You can also select data from a view if required.<\/p>\n\n\n\n
SELECT * INTO PetTypeCount\nFROM vPetTypeCount;<\/code><\/pre>\n\n\n\n
This selects data from the vPetTypeCount<\/code> view and inserts it into a new table called PetTypeCount<\/code>.<\/p>\n\n\n\n
We can verify this with a SELECT<\/code> statement.<\/p>\n\n\n\n
SELECT * FROM vPetTypeCount;\nSELECT * FROM PetTypeCount;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------+---------+\n| PetType \u00a0 | Count \u00a0 |\n|-----------+---------|\n| Bird\u00a0 \u00a0 \u00a0 | 1 \u00a0 \u00a0 \u00a0 |\n| Cat \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 |\n| Dog \u00a0 \u00a0 \u00a0 | 4 \u00a0 \u00a0 \u00a0 |\n+-----------+---------+\n(3 rows affected)\n+-----------+---------+\n| PetType \u00a0 | Count \u00a0 |\n|-----------+---------|\n| Bird\u00a0 \u00a0 \u00a0 | 1 \u00a0 \u00a0 \u00a0 |\n| Cat \u00a0 \u00a0 \u00a0 | 3 \u00a0 \u00a0 \u00a0 |\n| Dog \u00a0 \u00a0 \u00a0 | 4 \u00a0 \u00a0 \u00a0 |\n+-----------+---------+\n(3 rows affected)<\/pre>\n\n\n\n
DBMS Support<\/h2>\n\n\n\n
As mentioned, the SELECT INTO<\/code> statement is a Sybase extension, and it’s not supported by all major DBMSs. For example, MySQL and SQLite don’t support it.<\/p>\n\n\n\n
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).<\/p>\n\n\n\n
If your DBMS doesn’t support the SELECT INTO<\/code> statement, chances are it does support the INSERT INTO... SELECT<\/code> statement<\/a>, so you should try that instead.<\/p>\n","protected":false},"excerpt":{"rendered":"
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).<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[9,10,48,20],"class_list":["post-11708","post","type-post","status-publish","format-standard","hentry","category-sql","tag-create-table","tag-how-to","tag-sql","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/11708","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=11708"}],"version-history":[{"count":7,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/11708\/revisions"}],"predecessor-version":[{"id":11715,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/11708\/revisions\/11715"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=11708"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=11708"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=11708"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}