SELECT - INTO 子句 (Transact-SQL)SELECT - INTO Clause (Transact-SQL)

适用对象:SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

SELECT…INTO 在默认文件组中创建一个新表,并将来自查询的结果行插入该表中。SELECT...INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it. 要查看完整的 SELECT 语法,请参阅 SELECT (Transact-SQL)To view the complete SELECT syntax, see SELECT (Transact-SQL).

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

[ INTO new_table ]
[ ON filegroup ]

参数Arguments

new_table new_table
根据选择列表中的列和从数据源选择的行,指定要创建的新表名。Specifies the name of a new table to be created, based on the columns in the select list and the rows chosen from the data source.

new_table 的格式通过对选择列表中的表达式进行取值来确定 。The format of new_table is determined by evaluating the expressions in the select list. new_table 中的列按选择列表指定的顺序创建 。The columns in new_table are created in the order specified by the select list. new_table 中的每列与选择列表中的相应表达式具有相同的名称、数据类型、为 Null 性和值 。Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list. 列的 IDENTITY 属性将被转移,但在“备注”部分的“使用标识列”中定义的情况除外。The IDENTITY property of a column is transferred except under the conditions defined in "Working with Identity Columns" in the Remarks section.

要在 SQL ServerSQL Server 的同一实例上的另一个数据库中创建表,请将 new_table 指定为 database.schema.table_name 形式的完全限定名称 。To create the table in another database on the same instance of SQL ServerSQL Server, specify new_table as a fully qualified name in the form database.schema.table_name.

不能在远程计算机上创建 new_table,但可以从远程数据源填充 new_table 。You cannot create new_table on a remote server; however, you can populate new_table from a remote data source. 要从远程源表创建 new_table,请在 SELECT 语句的 FROM 子句中,按照 linked_server、catalog、schema 和 object 的形式使用由四个部分组成的名称,指定源表 。To create new_table from a remote source table, specify the source table using a four-part name in the form linked_server.catalog.schema.object in the FROM clause of the SELECT statement. 或者,也可以在 FROM 子句中使用 OPENQUERYOPENDATASOURCE 函数来指定远程数据源。Alternatively, you can use the OPENQUERY function or the OPENDATASOURCE function in the FROM clause to specify the remote data source.

filegroup filegroup
指定要在其中创建新表的文件组的名称。Specifies the name of the filegroup in which new table will be created. 指定的文件组应存在于数据库中,否则 SQL Server 引擎会引发错误。The filegroup specified should exist on the database else the SQL Server engine throws an error.

适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and later.

数据类型Data Types

FILESTREAM 属性不转移到新表。The FILESTREAM attribute does not transfer to the new table. FILESTREAM BLOB 作为 varbinary(max) BLOB 复制并存储在新表中 。FILESTREAM BLOBs are copied and stored in the new table as varbinary(max) BLOBs. 如果没有 FILESTREAM 属性,则 varbinary(max) 数据类型具有 2 GB 的限制 。Without the FILESTREAM attribute, the varbinary(max) data type has a limitation of 2 GB. 如果某个 FILESTREAM BLOB 超过该值,则会引发 7119 错误并停止该语句。If a FILESTREAM BLOB exceeds this value, error 7119 is raised and the statement is stopped.

在选择现有标识列并将其插入到新表时,新列将继承 IDENTITY 属性,除非满足以下条件之一:When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

  • SELECT 语句包含一个联接。The SELECT statement contains a join.

  • 多个 SELECT 语句由 UNION 联接。Multiple SELECT statements are joined by using UNION.

  • 标识列在选择列表内多次列出。The identity column is listed more than one time in the select list.

  • 标识列是表达式的一部分。The identity column is part of an expression.

  • 标识列来自远程数据源。The identity column is from a remote data source.

如果这些条件中的一个为真,列将被创建为 NOT NULL 而不继承 IDENTITY 属性。If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. 如果在新表中需要某一标识列,但此类列不可用,或者您需要不同于源标识列的种子或增量值,则使用 IDENTITY 函数在选择列表中定义该列。If an identity column is required in the new table but such a column is not available, or you want a seed or increment value that is different than the source identity column, define the column in the select list using the IDENTITY function. 请参阅下面的“示例”部分中的“使用 IDENTITY 函数创建标识列”。See "Creating an identity column using the IDENTITY function" in the Examples section below.

备注Remarks

SELECT...INTO 语句的操作分为两个部分:创建新表,然后插入行。The SELECT...INTO statement operates in two parts - the new table is created, and then rows are inserted. 这意味着如果插入失败,它们将全部回滚,但新的(空)表将保留。This means that if the inserts fail, they will all be rolled back, but the new (empty) table will remain. 如果需要整个操作以整体的形式成功或失败,请使用显式事务If you need the entire operation to succeed or fail as a whole, use an explicit transaction.

限制和局限Limitations and Restrictions

不能将表变量或表值参数指定为新表。You cannot specify a table variable or table-valued parameter as the new table.

即使已对源表进行分区,也不能使用 SELECT...INTO 创建已分区表。You cannot use SELECT...INTO to create a partitioned table, even when the source table is partitioned. SELECT...INTO 不使用源表的分区方案;而是在默认文件组中创建新表。SELECT...INTO does not use the partition scheme of the source table; instead, the new table is created in the default filegroup. 若要在已分区表中插入行,必须先创建已分区表,然后使用 INSERT INTO...SELECT...FROM 语句。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 语句中指定它们。Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT...INTO statement. 如果需要使用这些对象,可以在执行 SELECT...INTO 语句后创建它们。If these objects are required, you can create them after executing the SELECT...INTO statement.

指定 ORDER BY 子句无法确保行将按指定的顺序插入。Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order.

当选择列表中包含稀疏列时,稀疏列属性不会转移到新表中的列。When a sparse column is included in the select list, the sparse column property does not transfer to the column in the new table. 如果需要在新表中使用该属性,请在执行 SELECT...INTO 语句后更改列定义以包含该属性。If this property is required in the new table, alter the column definition after executing the SELECT...INTO statement to include this property.

当选择列表中包含计算列时,新表中的相应列并不是计算列。When a computed column is included in the select list, the corresponding column in the new table is not a computed column. 新列中的值是在执行 SELECT...INTO 时计算所得的值。The values in the new column are the values that were computed at the time SELECT...INTO was executed.

日志记录行为Logging Behavior

SELECT...INTO 的日志记录量取决于数据库的有效恢复模式。The amount of logging for SELECT...INTO depends on the recovery model in effect for the database. 在简单恢复模式或大容量日志恢复模式下,大容量操作是最小日志记录操作。Under the simple recovery model or bulk-logged recovery model, bulk operations are minimally logged. 对于最小的日志记录,使用 SELECT...INTO 语句可能比创建一个表后再使用 INSERT 语句填充该表的效率更高。With minimal logging, using the SELECT...INTO statement can be more efficient than creating a table and then populating the table with an INSERT statement. 有关详细信息,请参阅 事务日志 (SQL Server)For more information, see The Transaction Log (SQL Server).

权限Permissions

在目标数据库中要求 CREATE TABLE 权限。Requires CREATE TABLE permission in the destination database.

示例Examples

A.A. 通过指定来自多个源的列,创建一个表Creating a table by specifying columns from multiple sources

下面的示例通过从与雇员或地址有关的各个表中选择七列创建 AdventureWorks2012AdventureWorks2012 数据库中的 dbo.EmployeeAddresses 表。The following example creates the table dbo.EmployeeAddresses in the AdventureWorks2012AdventureWorks2012 database by selecting seven columns from various employee-related and address-related tables.

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  

B.B. 使用最小日志记录插入行Inserting rows using minimal logging

下面的示例创建 dbo.NewProducts 表并从 Production.Product 表插入行。The following example creates the table dbo.NewProducts and inserts rows from the Production.Product table. 此示例假定 AdventureWorks2012AdventureWorks2012 数据库的恢复模式设置为 FULL。The example assumes that the recovery model of the AdventureWorks2012AdventureWorks2012 database is set to FULL. 若要确保最小方式记录,应在插入行之前将 AdventureWorks2012AdventureWorks2012 数据库的恢复模式设置为 BULK_LOGGED,并在 SELECT...INTO 语句后重置为 FULL。To ensure minimal logging is used, the recovery model of the AdventureWorks2012AdventureWorks2012 database is set to BULK_LOGGED before rows are inserted and reset to FULL after the SELECT...INTO statement. 此过程确保 SELECT...INTO 语句在事务日志中占用最少的空间并且高效执行。This process ensures that the SELECT...INTO statement uses minimal space in the transaction log and performs efficiently.

ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;  
GO  
  
SELECT * INTO dbo.NewProducts  
FROM Production.Product  
WHERE ListPrice > $25   
AND ListPrice < $100;  
GO  
ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;  
GO  

C.C. 使用 IDENTITY 函数创建标识列Creating an identity column using the IDENTITY function

下面的示例使用 IDENTITY 函数在 AdventureWorks2012AdventureWorks2012 数据库的新表 Person.USAddress 中创建一个标识列。The following example uses the IDENTITY function to create an identity column in the new table Person.USAddress in the AdventureWorks2012AdventureWorks2012 database. 这是必需的,因为定义该表的 SELECT 语句包含一个联接,而该联接导致 IDENTITY 属性不转移到新表。This is required because the SELECT statement that defines the table contains a join, which causes the IDENTITY property to not transfer to the new table. 请注意,在 IDENTITY 函数中指定的种子和增量值不同于源表 AddressIDPerson.Address 列中的种子和增量值。Notice that the seed and increment values specified in the IDENTITY function are different from those of the AddressID column in the source table Person.Address.

-- Determine the IDENTITY status of the source column AddressID.  
SELECT OBJECT_NAME(object_id) AS TableName, name AS column_name, 
  is_identity, seed_value, increment_value  
FROM sys.identity_columns  
WHERE name = 'AddressID';  
  
-- Create a new table with columns from the existing table Person.Address. 
-- A new IDENTITY column is created by using the IDENTITY function.  
SELECT IDENTITY (int, 100, 5) AS AddressID,   
       a.AddressLine1, a.City, b.Name AS State, a.PostalCode  
INTO Person.USAddress   
FROM Person.Address AS a  
INNER JOIN Person.StateProvince AS b 
  ON a.StateProvinceID = b.StateProvinceID  
WHERE b.CountryRegionCode = N'US';   
  
-- Verify the IDENTITY status of the AddressID columns in both tables.  
SELECT OBJECT_NAME(object_id) AS TableName, name AS column_name, 
  is_identity, seed_value, increment_value  
FROM sys.identity_columns  
WHERE name = 'AddressID';  

D.D. 通过指定来自远程数据源的列,创建一个表Creating a table by specifying columns from a remote data source

下面的示例演示从远程数据源在本地服务器上创建新表的三个方法。The following example demonstrates three methods of creating a new table on the local server from a remote data source. 该示例从创建指向远程数据源的链接开始。The example begins by creating a link to the remote data source. 然后在第一个 SELECT...INTO 语句的 FROM 子句中和第二个 SELECT...INTO 语句的 OPENQUERY 函数中指定链接服务器名称 MyLinkServer,The linked server name, MyLinkServer, is then specified in the FROM clause of the first SELECT...INTO statement and in the OPENQUERY function of the second SELECT...INTO statement. 第三个 SELECT...INTO 语句使用 OPENDATASOURCE 函数,该函数直接指定远程数据源,而非使用链接的服务器名称。The third SELECT...INTO statement uses the OPENDATASOURCE function, which specifies the remote data source directly instead of using the linked server name.

适用于:SQL Server 2008SQL Server 2008 及更高版本 。Applies to: SQL Server 2008SQL Server 2008 and later.

USE master;  
GO  
-- Create a link to the remote data source.   
-- Specify a valid server name for @datasrc as 'server_name' 
-- or 'server_name\instance_name'.  
EXEC sp_addlinkedserver @server = N'MyLinkServer',  
    @srvproduct = N' ',  
    @provider = N'SQLNCLI',   
    @datasrc = N'server_name',  
    @catalog = N'AdventureWorks2012';  
GO  

USE AdventureWorks2012;  
GO  
-- Specify the remote data source in the FROM clause using a four-part name   
-- in the form linked_server.catalog.schema.object.  
SELECT DepartmentID, Name, GroupName, ModifiedDate  
INTO dbo.Departments  
FROM MyLinkServer.AdventureWorks2012.HumanResources.Department  
GO  
-- Use the OPENQUERY function to access the remote data source.  
SELECT DepartmentID, Name, GroupName, ModifiedDate  
INTO dbo.DepartmentsUsingOpenQuery  
FROM OPENQUERY(MyLinkServer, 'SELECT *  
               FROM AdventureWorks2012.HumanResources.Department');   
GO  
-- Use the OPENDATASOURCE function to specify the remote data source.  
-- Specify a valid server name for Data Source using the format 
-- server_name or server_name\instance_name.  
SELECT DepartmentID, Name, GroupName, ModifiedDate  
INTO dbo.DepartmentsUsingOpenDataSource  
FROM OPENDATASOURCE('SQLNCLI',  
    'Data Source=server_name;Integrated Security=SSPI')  
    .AdventureWorks2012.HumanResources.Department;  
GO  

E.E. 从使用 PolyBase 创建的外部表导入Import from an external table created with PolyBase

从 Hadoop 或 Azure 存储空间将数据导入到 SQL Server 进行永久存储。Import data from Hadoop or Azure Storage into SQL Server for persistent storage. 使用 SELECT INTO 导入外部表引用的数据,以便永久存储在 SQL Server 中。Use SELECT INTO to import data referenced by an external table for persistent storage in SQL Server. 动态创建关系表,然后在第二步中创建基于该表的列存储索引。Create a relational table on-the-fly and then create a column-store index on top of the table in a second step.

适用对象:SQL Server 2019 (15.x)SQL Server 2019 (15.x)Applies to: SQL Server 2019 (15.x)SQL Server 2019 (15.x).

-- Import data for car drivers into SQL Server to do more in-depth analysis.  
SELECT DISTINCT   
        Insured_Customers.FirstName, Insured_Customers.LastName,   
        Insured_Customers.YearlyIncome, Insured_Customers.MaritalStatus  
INTO Fast_Customers from Insured_Customers INNER JOIN   
(  
        SELECT * FROM CarSensor_Data where Speed > 35   
) AS SensorD  
ON Insured_Customers.CustomerKey = SensorD.CustomerKey  
ORDER BY YearlyIncome;  

F.F. 创建一个新表作为另一个表的副本并将其加载到指定的文件组Creating a new table as a copy of another table and loading it a specified filegroup

以下示例演示如何创建一个新表作为另一个表的副本,并将其加载到用户默认文件组之外的指定文件组。The following example demonstrates creating a new table as a copy of another table and loading it into a specified filegroup different from the default filegroup of the user.

适用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 和更高版本。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 and later.

ALTER DATABASE [AdventureWorksDW2016] ADD FILEGROUP FG2;
ALTER DATABASE [AdventureWorksDW2016]
ADD FILE
(
NAME='FG2_Data',
FILENAME = '/var/opt/mssql/data/AdventureWorksDW2016_Data1.mdf'
)
TO FILEGROUP FG2;
GO
SELECT * INTO [dbo].[FactResellerSalesXL] ON FG2 FROM [dbo].[FactResellerSales];

另请参阅See Also

SELECT (Transact-SQL) SELECT (Transact-SQL)
SELECT 示例 (Transact-SQL) SELECT Examples (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
IDENTITY(函数)(Transact-SQL)IDENTITY (Function) (Transact-SQL)