SQL Select into Statement


The SELECT INTO statement is a query that allows you to create a new table and populate it with the result set of a SELECT statement. To add data to an existing table, see the INSERT INTO statement instead.

SELECT INTO can be used when you are combining data from several tables or views into a new table.1 The original table is not affected.

The general syntax is:

SELECT column-names INTO new-table-name FROM table-name WHERE EXISTS (SELECT column-name FROM table-name        WHERE condition)

This example shows a set of a table that was “copied” from the “Supplier” table to a new one called SupplierUSA which holds the set related to the column country of value ‘USA’.

SELECT * INTO SupplierUSA FROM Supplier WHERE Country = 'USA';

Results: 4 rows affected 2

IDCompanyNameContactNameCityCountryPhone
2New Orleans Cajun DelightsShelley BurkeNew OrleansUSA(100) 555-4822
3Grandma Kelly’s HomesteadRegina MurphyAnn ArborUSA(313) 555-5735
16Bigfoot BreweriesCheryl SaylorBendUSANULL
19New England Seafood CanneryRobb MerchantBostonUSA(617) 555-3267

Please see the manual for your database manager and have fun trying different options yourself.

Sources

  1. (Microsoft – Inserting Rows by Using SELECT INTO)[https://technet.microsoft.com/en-us/library/ms190750(v=sql.105).aspx]
  2. (dofactory – SQL SELECT INTO Statement)[http://www.dofactory.com/sql/select-into]

This article needs improvement. You can help improve this article. You can also write similar articles and help the community.