Share via

INTO Clause (Transact-SQL)

  • Article

Creates a new table and inserts the resulting rows from the query into it.

Topic link iconTransact-SQL Syntax Conventions

Syntax

[ INTO new_table ]

Arguments

  • new_table
    Specifies the name of a new table to be created, based on the columns in the select list and the rows chosen by the WHERE clause. The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, and value as the corresponding expression in the select list.

    When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT...INTO was executed.

    The amount of logging for certain bulk operations, including SELECT...INTO, depends on the recovery model in effect for the database. For more information, see Overview of the Recovery Models.

    Note

    In releases before SQL Server 2000, creating a permanent table with SELECT INTO is allowed only if select into/bulkcopy is set. In SQL Server 2000 and later, the select into/bulkcopy database option no longer affects whether you can create a permanent table by using SELECT INTO.

Remarks

The user that executes a SELECT statement with the INTO clause must have CREATE TABLE permission in the destination database.

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.

SELECT...INTO cannot be used with COMPUTE.

Examples

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

See Also

Reference

SELECT (Transact-SQL)
SELECT Examples (Transact-SQL)

Other Resources

Inserting Rows by Using SELECT INTO

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

15 September 2007
New content:
  • Added partitioned table information in the Remarks.
  • Added the Examples section.