Inserting Rows by Using SELECT INTO
The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server.
The structure of the new table is defined by the attributes of the expressions in the select list. The following example creates the table dbo.EmployeeAddresses by selecting seven columns from various employee and address-related tables.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title, a.AddressLine1, a.City, sp.Name AS [State/Province], a.PostalCode
INTO dbo.EmployeeAddresses
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e ON e.ContactID = c.ContactID
JOIN HumanResources.EmployeeAddress AS ea ON ea.EmployeeID = e.EmployeeID
JOIN Person.Address AS a on a.AddressID = ea.AddressID
JOIN Person.StateProvince as sp ON sp.StateProvinceID = a.StateProvinceID;
GO
You cannot use SELECT INTO to create a partitioned table even when the source table is partitioned. SELECT INTO does not use the partition scheme of the source table. Instead, the new table is created in the default filegroup. To insert rows into a partitioned table, you must first create the partitioned table and then use the INSERT INTO…SELECT FROM statement.
See Also
Concepts
Adding Rows by Using INSERT and SELECT
Inserting a Row by Using INSERT and Values
Limiting Inserted Rows by Using TOP
Inserting Rows by Using INSERT and SELECT Subqueries