Share via

  • Article

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 AdventureWorks2008R2;
GO
SELECT c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, a.City, 
    sp.Name AS [State/Province], a.PostalCode
INTO dbo.EmployeeAddresses
FROM Person.Person AS c
    JOIN HumanResources.Employee AS e 
    ON e.BusinessEntityID = c.BusinessEntityID
    JOIN Person.BusinessEntityAddress AS bea
    ON e.BusinessEntityID = bea.BusinessEntityID
    JOIN Person.Address AS a
    ON bea.AddressID = a.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."