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.

The FILESTREAM attribute does not transfer when you create a new table by using the SELECT INTO statement. FILESTREAM BLOBs are copied and stored in the new table as varbinary(max) BLOBs. If a FILESTREAM BLOB exceeds 2 GB, the following error message is raised and the statement is stopped: "Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes."