INSERT (Transact-SQL)INSERT (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

将一行或多行添加到 SQL ServerSQL Server 的表或视图中。Adds one or more rows to a table or a view in SQL ServerSQL Server. 有关示例,请参阅示例For examples, see Examples.

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

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  

[ WITH <common_table_expression> [ ,...n ] ]  
INSERT   
{  
        [ TOP ( expression ) [ PERCENT ] ]   
        [ INTO ]   
        { <object> | rowset_function_limited   
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]  
        }  
    {  
        [ ( column_list ) ]   
        [ <OUTPUT Clause> ]  
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ]   
        | derived_table   
        | execute_statement  
        | <dml_table_source>  
        | DEFAULT VALUES   
        }  
    }  
}  
[;]  
  
<object> ::=  
{   
    [ server_name . database_name . schema_name .   
      | database_name .[ schema_name ] .   
      | schema_name .   
    ]  
  table_or_view_name  
}  
  
<dml_table_source> ::=  
    SELECT <select_list>  
    FROM ( <dml_statement_with_output_clause> )   
      [AS] table_alias [ ( column_alias [ ,...n ] ) ]  
    [ WHERE <search_condition> ]  
        [ OPTION ( <query_hint> [ ,...n ] ) ]  
-- External tool only syntax  

INSERT   
{  
    [BULK]  
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }  
    ( <column_definition> )  
    [ WITH (  
        [ [ , ] CHECK_CONSTRAINTS ]  
        [ [ , ] FIRE_TRIGGERS ]  
        [ [ , ] KEEP_NULLS ]  
        [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]  
        [ [ , ] ROWS_PER_BATCH = rows_per_batch ]  
        [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]  
        [ [ , ] TABLOCK ]  
    ) ]  
}  
  
[; ] <column_definition> ::=  
 column_name <data_type>  
    [ COLLATE collation_name ]  
    [ NULL | NOT NULL ]  
  
<data type> ::=   
[ type_schema_name . ] type_name   
    [ ( precision [ , scale ] | max ]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  

INSERT INTO { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ( column_name [ ,...n ] ) ]  
    {   
      VALUES ( { NULL | expression } )  
      | SELECT <select_criteria>  
    }  
    [ OPTION ( <query_option> [ ,...n ] ) ]  
[;]  

参数Arguments

WITH <common_table_expression>WITH <common_table_expression>
指定在 INSERT 语句作用域内定义的临时命名结果集(也称为公用表表达式)。Specifies the temporary named result set, also known as common table expression, defined within the scope of the INSERT statement. 结果集源自 SELECT 语句。The result set is derived from a SELECT statement. 有关详细信息,请参阅 WITH common_table_expression (Transact-SQL)For more information, see WITH common_table_expression (Transact-SQL).

TOP (expression) [ PERCENT ] TOP (expression) [ PERCENT ]
指定将插入的随机行的数目或百分比。Specifies the number or percent of random rows that will be inserted. expression 可以是行数或行的百分比。expression can be either a number or a percent of the rows. 有关详细信息,请参阅 TOP (Transact-SQL)For more information, see TOP (Transact-SQL).

INTOINTO
一个可选的关键字,可以将它用在 INSERT 和目标表之间。Is an optional keyword that can be used between INSERT and the target table.

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

表或视图所在的链接服务器的名称。Is the name of the linked server on which the table or view is located. server_name 可以指定为链接服务器名称,或通过使用 OPENDATASOURCE 函数 。server_name can be specified as a linked server name, or by using the OPENDATASOURCE function.

如果将 server_name 指定为链接服务器,则需要 database_name 和 schema_name 。When server_name is specified as a linked server, database_name and schema_name are required. 如果使用 OPENDATASOURCE 指定 server_name,则 database_name 和 schema_name 可能不适用于所有数据源,并且受到访问远程对象的 OLE DB 访问接口的性能的限制 。When server_name is specified with OPENDATASOURCE, database_name and schema_name may not apply to all data sources and is subject to the capabilities of the OLE DB provider that accesses the remote object.

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

数据库的名称。Is the name of the database.

schema_nameschema_name
表或视图所属架构的名称。Is the name of the schema to which the table or view belongs.

table_or view_name table_or view_name
要接收数据的表或视图的名称。Is the name of the table or view that is to receive the data.

变量在其作用域内可用作 INSERT 语句中的表源。A table variable, within its scope, can be used as a table source in an INSERT statement.

table_or_view_name 引用的视图必须可更新,并且只在该视图的 FROM 子句中引用一个基表 。The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view. 例如,多表视图中的 INSERT 必须使用只引用一个基表中的各列的 column_list 。For example, an INSERT into a multi-table view must use a column_list that references only columns from one base table. 有关可更新视图的详细信息,请参阅 CREATE VIEW (Transact-SQL)For more information about updatable views, see CREATE VIEW (Transact-SQL).

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

OPENQUERYOPENROWSET 函数。Is either the OPENQUERY or OPENROWSET function. 使用这些函数受到访问远程对象的 OLE DB 访问接口的性能的限制。Use of these functions is subject to the capabilities of the OLE DB provider that accesses the remote object.

WITH ( <table_hint_limited> [... n ] )WITH ( <table_hint_limited> [... n ] )
指定目标表允许的一个或多个表提示。Specifies one or more table hints that are allowed for a target table. 需要有 WITH 关键字和括号。The WITH keyword and the parentheses are required.

不允许 READPAST、NOLOCK 和 READUNCOMMITTED。READPAST, NOLOCK, and READUNCOMMITTED are not allowed. 有关表提示的详细信息,请参阅表提示 (Transact-SQL)For more information about table hints, see Table Hints (Transact-SQL).

重要

在将来的 SQL ServerSQL Server 版本中,将删除对作为 INSERT 语句目标的表指定 HOLDLOCK、SERIALIZABLE、READCOMMITTED、REPEATABLEREAD 或 UPDLOCK 提示的功能。The ability to specify the HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD, or UPDLOCK hints on tables that are targets of INSERT statements will be removed in a future version of SQL ServerSQL Server. 这些提示不影响 INSERT 语句的性能。These hints do not affect the performance of INSERT statements. 请避免在新的开发工作中使用该功能,并计划修改当前使用该功能的应用程序。Avoid using them in new development work, and plan to modify applications that currently use them.

对作为 INSERT 语句目标的表指定 TABLOCK 提示与指定 TABLOCKX 提示具有相同的效果。Specifying the TABLOCK hint on a table that is the target of an INSERT statement has the same effect as specifying the TABLOCKX hint. 对表采用排他锁。An exclusive lock is taken on the table.

(column_list)(column_list)
要在其中插入数据的一列或多列的列表。Is a list of one or more columns in which to insert data. 必须用括号将 column_list 括起来,并且用逗号进行分隔 。column_list must be enclosed in parentheses and delimited by commas.

如果某列不在 column_list 中,则 数据库引擎Database Engine 必须能够基于该列的定义提供一个值;否则不能加载行 。If a column is not in column_list, the 数据库引擎Database Engine must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. 如果列满足下面的条件,则数据库引擎Database Engine将自动为列提供值:The 数据库引擎Database Engine automatically provides a value for the column if the column:

  • 具有 IDENTITY 属性。Has an IDENTITY property. 使用下一个增量标识值。The next incremental identity value is used.

  • 有默认值。Has a default. 使用列的默认值。The default value for the column is used.

  • 具有 timestamp 数据类型 。Has a timestamp data type. 使用当前的时间戳值。The current timestamp value is used.

  • 可以为 Null。Is nullable. 使用 Null 值。A null value is used.

  • 是计算列。Is a computed column. 使用计算值。The calculated value is used.

当向标识列中插入显式值时,必须使用 column_list,并且表的 SET IDENTITY_INSERT 选项必须为 ON 。column_list must be used when explicit values are inserted into an identity column, and the SET IDENTITY_INSERT option must be ON for the table.

OUTPUT 子句OUTPUT Clause
将插入行作为插入操作的一部分返回。Returns inserted rows as part of the insert operation. 结果可返回到处理应用程序或插入到表或表变量中以供进一步处理。The results can be returned to the processing application or inserted into a table or table variable for further processing.

引用本地分区视图、分布式分区视图或远程表的 DML 语句或包含 execute_statement 的 INSERT 语句都不支持 OUTPUT 子句The OUTPUT clause is not supported in DML statements that reference local partitioned views, distributed partitioned views, or remote tables, or INSERT statements that contain an execute_statement. 包含 <dml_table_source> 子句的 INSERT 语句中不支持 OUTPUT INTO 子句。The OUTPUT INTO clause is not supported in INSERT statements that contain a <dml_table_source> clause.

VALUESVALUES
引入要插入的数据值的一个或多个列表。Introduces the list or lists of data values to be inserted. 对于 column_list(如果已指定)或表中的每个列,都必须有一个数据值 。There must be one data value for each column in column_list, if specified, or in the table. 必须用圆括号将值列表括起来。The value list must be enclosed in parentheses.

如果值列表中的各值与表中各列的顺序不相同,或者未包含表中各列的值,则必须使用 column_list 显式指定存储每个传入值的列 。If the values in the Value list are not in the same order as the columns in the table or do not have a value for each column in the table, column_list must be used to explicitly specify the column that stores each incoming value.

您可以使用 Transact-SQLTransact-SQL 行构造函数(又称为表值构造函数)在一个 INSERT 语句中指定多个行。You can use the Transact-SQLTransact-SQL row constructor (also called a table value constructor) to specify multiple rows in a single INSERT statement. 行构造函数包含一个 VALUES 子句和多个括在圆括号中且以逗号分隔的值列表。The row constructor consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma. 有关详细信息,请参阅表值构造函数 (Transact-SQL)For more information, see Table Value Constructor (Transact-SQL).

DEFAULTDEFAULT
强制数据库引擎Database Engine加载为列定义的默认值。Forces the 数据库引擎Database Engine to load the default value defined for a column. 如果某列并不存在默认值,并且该列允许 Null 值,则插入 NULL。If a default does not exist for the column and the column allows null values, NULL is inserted. 对于使用 timestamp 数据类型定义的列,插入下一个时间戳值 。For a column defined with the timestamp data type, the next timestamp value is inserted. DEFAULT 对标识列无效。DEFAULT is not valid for an identity column.

expressionexpression
一个常量、变量或表达式。Is a constant, a variable, or an expression. 表达式不能包含 EXECUTE 语句。The expression cannot contain an EXECUTE statement.

当引用 Unicode 字符数据类型 nchar 、nvarchar 和 ntext 时,“expression”应采用大写字母“N”作为前缀 。When referencing the Unicode character data types nchar, nvarchar, and ntext, 'expression' should be prefixed with the capital letter 'N'. 如果未指定“N”,则 SQL ServerSQL Server 会将字符串转换为与数据库或列的默认排序规则相对应的代码页。If 'N' is not specified, SQL ServerSQL Server converts the string to the code page that corresponds to the default collation of the database or column. 此代码页中没有的字符都将丢失。Any characters not found in this code page are lost.

derived_table derived_table
任何有效的 SELECT 语句,它返回将加载到表中的数据行。Is any valid SELECT statement that returns rows of data to be loaded into the table. SELECT 语句不能包含公用表表达式 (CTE)。The SELECT statement cannot contain a common table expression (CTE).

execute_statementexecute_statement
任何有效的 EXECUTE 语句,它使用 SELECT 或 READTEXT 语句返回数据。Is any valid EXECUTE statement that returns data with SELECT or READTEXT statements. 有关详细信息,请参阅 EXECUTE (Transact-SQL)For more information, see EXECUTE (Transact-SQL).

不能在 INSERT…EXEC 语句中指定 EXECUTE 语句的 RESULT SETS 选项。The RESULT SETS options of the EXECUTE statement cannot be specified in an INSERT...EXEC statement.

如果 execute_statement 使用 INSERT,则每个结果集必须与表或 column_list 中的列兼容 。If execute_statement is used with INSERT, each result set must be compatible with the columns in the table or in column_list.

可以使用 execute_statement 对同一服务器或远程服务器执行存储过程 。execute_statement can be used to execute stored procedures on the same server or a remote server. 执行远程服务器中的过程,并将结果集返回到本地服务器并加载到本地服务器的表中。The procedure in the remote server is executed, and the result sets are returned to the local server and loaded into the table in the local server. 在分布式事务中,当连接启用了多个活动结果集 (MARS) 时,无法针对环回链接服务器发出 execute_statement 。In a distributed transaction, execute_statement cannot be issued against a loopback linked server when the connection has multiple active result sets (MARS) enabled.

如果 execute_statement 使用 READTEXT 语句返回数据,则每个 READTEXT 语句最多可以返回 1 MB (1024 KB) 的数据 。If execute_statement returns data with the READTEXT statement, each READTEXT statement can return a maximum of 1 MB (1024 KB) of data. execute_statement 还可以用于扩展过程 。execute_statement can also be used with extended procedures. execute_statement 插入由扩展过程的主线程返回的数据,但不插入主线程以外的线程的输出 。execute_statement inserts the data returned by the main thread of the extended procedure; however, output from threads other than the main thread are not inserted.

不能将表值参数指定为 INSERT EXEC 语句的目标;但是,可以将它指定为 INSERT EXEC 字符串或存储过程中的源。You cannot specify a table-valued parameter as the target of an INSERT EXEC statement; however, it can be specified as a source in the INSERT EXEC string or stored-procedure. 有关详细信息,请参阅使用表值参数(数据引擎)For more information, see Use Table-Valued Parameters (Database Engine).

<dml_table_source><dml_table_source>
指定插入目标表的行是 INSERT、UPDATE、DELETE 或 MERGE 语句的 OUTPUT 子句返回的行;可以通过 WHERE 子句对行进行筛选。Specifies that the rows inserted into the target table are those returned by the OUTPUT clause of an INSERT, UPDATE, DELETE, or MERGE statement, optionally filtered by a WHERE clause. 如果指定了 <dml_table_source>,外部 INSERT 语句的目标必须满足以下限制:If <dml_table_source> is specified, the target of the outer INSERT statement must meet the following restrictions:

  • 必须是基表而不是视图。It must be a base table, not a view.

  • 不能是远程表。It cannot be a remote table.

  • 不能对其定义任何触发器。It cannot have any triggers defined on it.

  • 不能参与任何主键-外键关系。It cannot participate in any primary key-foreign key relationships.

  • 不能参与合并复制或事务复制的可更新订阅。It cannot participate in merge replication or updatable subscriptions for transactional replication.

数据库的兼容级别必须设置为 100 或更高。The compatibility level of the database must be set to 100 or higher. 有关详细信息,请参阅 OUTPUT 子句 (Transact-SQL)For more information, see OUTPUT Clause (Transact-SQL).

<select_list><select_list>
指定要插入 OUTPUT 子句所返回的列的逗号分隔列表。Is a comma-separated list specifying which columns returned by the OUTPUT clause to insert. <select_list> 中的列必须与要插入值的列兼容。The columns in <select_list> must be compatible with the columns into which values are being inserted. <select_list>无法引用聚合函数或 TEXTPTR。<select_list> cannot reference aggregate functions or TEXTPTR.

备注

无论在 <dml_statement_with_output_clause> 中对 SELECT 列表中列出的任何变量做何种更改,这些变量都将引用其原始值。Any variables listed in the SELECT list refer to their original values, regardless of any changes made to them in <dml_statement_with_output_clause>.

<dml_statement_with_output_clause><dml_statement_with_output_clause>
在 OUTPUT 子句中返回受影响行的有效 INSERT、UPDATE、DELETE 或 MERGE 语句。Is a valid INSERT, UPDATE, DELETE, or MERGE statement that returns affected rows in an OUTPUT clause. 语句中不能包含 WITH 子句,且不能以远程表或分区视图为目标。The statement cannot contain a WITH clause, and cannot target remote tables or partitioned views. 如果指定了 UPDATE 或 DELETE,则所指定的 UPDATE 或 DELETE 不能是基于游标的。If UPDATE or DELETE is specified, it cannot be a cursor-based UPDATE or DELETE. 源行不能作为嵌套的 DML 语句进行引用。Source rows cannot be referenced as nested DML statements.

WHERE <search_condition>WHERE <search_condition>
任意 WHERE 子句,其中包含对 <dml_statement_with_output_clause> 返回的行进行筛选的有效 <search_condition>。Is any WHERE clause containing a valid <search_condition> that filters the rows returned by <dml_statement_with_output_clause>. 有关详细信息,请参阅搜索条件 (Transact-SQL)For more information, see Search Condition (Transact-SQL). 在此上下文中使用时,<search_condition> 不能包含子查询、执行数据访问的标量用户定义函数、聚合函数、TEXTPTR 或全文搜索谓词。When used in this context, <search_condition> cannot contain subqueries, scalar user-defined functions that perform data access, aggregate functions, TEXTPTR, or full-text search predicates.

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

强制新行包含为每个列定义的默认值。Forces the new row to contain the default values defined for each column.

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

由外部工具用来上载二进制数据流。Used by external tools to upload a binary data stream. 该选项并不旨在用于 SQL Server Management StudioSQL Server Management Studio、SQLCMD、OSQL 之类的工具或者 SQL ServerSQL Server Native Client 之类的数据访问应用程序编程接口。This option is not intended for use with tools such as SQL Server Management StudioSQL Server Management Studio, SQLCMD, OSQL, or data access application programming interfaces such as SQL ServerSQL Server Native Client.

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

指定将在二进制数据流上载操作期间执行目标表中定义的所有插入触发器。Specifies that any insert triggers defined on the destination table execute during the binary data stream upload operation. 有关详细信息,请参阅 BULK INSERT (Transact SQL)For more information, see BULK INSERT (Transact-SQL).

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

指定在二进制数据流上载操作期间,必须检查所有对目标表或视图的约束。Specifies that all constraints on the target table or view must be checked during the binary data stream upload operation. 有关详细信息,请参阅 BULK INSERT (Transact SQL)For more information, see BULK INSERT (Transact-SQL).

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

指定在二进制数据流上载操作期间空列应该保留 null 值。Specifies that empty columns should retain a null value during the binary data stream upload operation. 有关详细信息,请参阅在批量导入期间保留 Null 或使用默认值 (SQL Server)For more information, see Keep Nulls or Use Default Values During Bulk Import (SQL Server).

KILOBYTES_PER_BATCH = kilobytes_per_batchKILOBYTES_PER_BATCH = kilobytes_per_batch
将每个批处理中数据的近似千字节数 (KB) 指定为 kilobytes_per_batch 。Specifies the approximate number of kilobytes (KB) of data per batch as kilobytes_per_batch. 有关详细信息,请参阅 BULK INSERT (Transact SQL)For more information, see BULK INSERT (Transact-SQL).

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

指示二进制数据流中近似的数据行数量。Indicates the approximate number of rows of data in the binary data stream. 有关详细信息,请参阅 BULK INSERT (Transact SQL)For more information, see BULK INSERT (Transact-SQL).

备注

如果未提供列列表,则引发一个语法错误。A syntax error is raised if a column list is not provided.

RemarksRemarks

有关将数据插入 SQL 图表的详细信息,请参阅 INSERT(SQL 图形)For information specific to inserting data into SQL graph tables, see INSERT (SQL Graph).

最佳实践Best Practices

使用 @@ROWCOUNT 函数返回插入到客户端应用程序的行数。Use the @@ROWCOUNT function to return the number of inserted rows to the client application. 有关详细信息,请参阅 @@ROWCOUNT (Transact-SQL)For more information, see @@ROWCOUNT (Transact-SQL).

大容量导入数据的最佳实践Best Practices for Bulk Importing Data

使用 INSERT INTO…SELECT 进行大容量导入数据并按最小方式记录日志Using INSERT INTO...SELECT to Bulk Import Data with Minimal Logging

可以使用 INSERT INTO <target_table> SELECT <columns> FROM <source_table> 高效地将大量行从一个表(例如临时表)传输到按最小方式记录日志的其他表中。You can use INSERT INTO <target_table> SELECT <columns> FROM <source_table> to efficiently transfer a large number of rows from one table, such as a staging table, to another table with minimal logging. 按最小方式记录日志可以提高语句的性能,减少在事务期间此操作填充可用事务日志空间的可能性。Minimal logging can improve the performance of the statement and reduce the possibility of the operation filling the available transaction log space during the transaction.

针对此语句的按最小方式记录日志具有以下要求:Minimal logging for this statement has the following requirements:

  • 数据库的恢复模式设置为简单或大容量日志模式。The recovery model of the database is set to simple or bulk-logged.

  • 目标表是空或非空堆。The target table is an empty or nonempty heap.

  • 复制操作未使用目标表。The target table is not used in replication.

  • 为目标表指定了 TABLOCK 提示。The TABLOCK hint is specified for the target table.

此外,可能还可以以最小方式记录通过 MERGE 语句中的插入操作插入堆中的行。Rows that are inserted into a heap as the result of an insert action in a MERGE statement may also be minimally logged.

与持有较少限制性大容量更新锁的 BULK INSERT 语句不同,具有 TABLOCK 提示的 INSERT INTO…SELECT 语句持有一个针对表的排他 (X) 锁。Unlike the BULK INSERT statement, which holds a less restrictive Bulk Update lock, INSERT INTO...SELECT with the TABLOCK hint holds an exclusive (X) lock on the table. 也就是说您不能使用并行插入操作插入行。This means that you cannot insert rows using parallel insert operations.

使用 OPENROWSET 和 BULK 大容量导入数据Using OPENROWSET and BULK to Bulk Import Data

OPENROWSET 函数可接受以下表提示,这些表提示使用 INSERT 语句提供大容量加载优化:The OPENROWSET function can accept the following table hints, which provide bulk-load optimizations with the INSERT statement:

  • TABLOCK 提示可以最大限度减少插入操作的日志记录数量。The TABLOCK hint can minimize the number of log records for the insert operation. 数据库的恢复模式必须设置为简单或大容量日志模式,并且目标表不能用于复制。The recovery model of the database must be set to simple or bulk-logged and the target table cannot be used in replication. 有关详细信息,请参阅在批量导入中按最小方式记录日志的前提条件For more information, see Prerequisites for Minimal Logging in Bulk Import.

  • IGNORE_CONSTRAINTS 提示可以暂时禁用 FOREIGN KEY 和 CHECK 约束检查。The IGNORE_CONSTRAINTS hint can temporarily disable FOREIGN KEY and CHECK constraint checking.

  • IGNORE_TRIGGERS 提示可以暂时禁用触发器执行。The IGNORE_TRIGGERS hint can temporarily disable trigger execution.

  • KEEPDEFAULTS 提示允许数据记录在某一表列缺少值时插入此列的默认值(如果有),而不是插入 NULL。The KEEPDEFAULTS hint allows the insertion of a table column's default value, if any, instead of NULL when the data record lacks a value for the column.

  • KEEPIDENTITY 提示允许导入数据文件中的标识值用于目标表中的标识列。The KEEPIDENTITY hint allows the identity values in the imported data file to be used for the identity column in the target table.

这些优化类似于可与 BULK INSERT 命令一起使用的优化。These optimizations are similar to those available with the BULK INSERT command. 有关详细信息,请参阅表提示 (Transact-SQL)For more information, see Table Hints (Transact-SQL).

数据类型Data Types

插入行时,考虑以下数据类型行为:When you insert rows, consider the following data type behavior:

  • 如果将值加载到 char、varchar 或 varbinary 数据类型的列中,则尾随空格(对于 char 和 varchar 为空格,对于 varbinary 为零)的填充或截断由创建表时为该列定义的 SET ANSI_PADDING 设置确定 。If a value is being loaded into columns with a char, varchar, or varbinary data type, the padding or truncation of trailing blanks (spaces for char and varchar, zeros for varbinary) is determined by the SET ANSI_PADDING setting defined for the column when the table was created. 有关详细信息,请参阅 SET ANSI_PADDING (Transact-SQL)For more information, see SET ANSI_PADDING (Transact-SQL).

    下表显示了 SET ANSI_PADDING OFF 的默认操作。The following table shows the default operation for SET ANSI_PADDING OFF.

    数据类型Data type 默认操作Default operation
    charchar 将带有空格的值填充到已定义的列宽。Pad value with spaces to the defined width of column.
    varcharvarchar 删除最后的非空格字符后面的尾随空格,而对于只由空格组成的字符串,一直删除到只留下一个空格。Remove trailing spaces to the last non-space character or to a single-space character for strings made up of only spaces.
    varbinaryvarbinary 删除尾随的零。Remove trailing zeros.
  • 如果将一个空字符串 (' ') 加载到数据类型为 varchar 或 text 的列,则默认操作是加载一个零长度的字符串 。If an empty string (' ') is loaded into a column with a varchar or text data type, the default operation is to load a zero-length string.

  • 将 Null 值插入到 text 或 image 列不创建有效的文本指针,也不预分配 8 KB 的文本页 。Inserting a null value into a text or image column does not create a valid text pointer, nor does it preallocate an 8-KB text page.

  • 使用 uniqueidentifier 数据类型创建的列存储特殊格式的 16 字节二进制值 。Columns created with the uniqueidentifier data type store specially formatted 16-byte binary values. 与标识列不同,数据库引擎Database Engine 不为 uniqueidentifier 数据类型的列自动生成值 。Unlike with identity columns, the 数据库引擎Database Engine does not automatically generate values for columns with the uniqueidentifier data type. 在插入操作过程中,可以将 uniqueidentifier 数据类型的变量和 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 格式的字符串常量(包括连字符在内共 36 个字符,其中 x 表示从 0 到 9 或从 a 到 f 的十六进制数字)用于 uniqueidentifier 列 。During an insert operation, variables with a data type of uniqueidentifier and string constants in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (36 characters including hyphens, where x is a hexadecimal digit in the range 0-9 or a-f) can be used for uniqueidentifier columns. 例如,6F9619FF-8B86-D011-B42D-00C04FC964FF 是 uniqueidentifier 变量或列的有效值 。For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid value for a uniqueidentifier variable or column. 使用 NEWID() 函数获取全局唯一 ID (GUID)。Use the NEWID() function to obtain a globally unique ID (GUID).

将值插入到用户定义类型列中Inserting Values into User-Defined Type Columns

可以通过以下方法将值插入到用户定义的类型列中:You can insert values in user-defined type columns by:

  • 提供用户定义类型的值。Supplying a value of the user-defined type.

  • 提供 SQL ServerSQL Server 系统数据类型的值,条件是该用户定义类型支持该类型的隐式转换或显式转换。Supplying a value in a SQL ServerSQL Server system data type, as long as the user-defined type supports implicit or explicit conversion from that type. 下面的示例演示了如何基于字符串进行显式转换将值插入到用户定义的类型 Point 的列中。The following example shows how to insert a value in a column of user-defined type Point, by explicitly converting from a string.

    INSERT INTO Cities (Location)  
    VALUES ( CONVERT(Point, '12.3:46.2') );  
    

    由于所有用户定义的类型可以从二进制值进行隐式转换,因此还可以在不执行显式转换的情况下提供二进制值。A binary value can also be supplied without performing explicit conversion, because all user-defined types are implicitly convertible from binary.

  • 调用一个用户定义函数,该函数返回用户定义类型的值。Calling a user-defined function that returns a value of the user-defined type. 下面的示例使用用户定义函数 CreateNewPoint() 创建一个用户定义类型 Point 的新值,并将该值插入到 Cities 表中。The following example uses a user-defined function CreateNewPoint() to create a new value of user-defined type Point and insert the value into the Cities table.

    INSERT INTO Cities (Location)  
    VALUES ( dbo.CreateNewPoint(x, y) );  
    

错误处理Error Handling

可以通过在 TRY…CATCH 构造函数中指定 INSERT 语句,实现对该语句的错误处理。You can implement error handling for the INSERT statement by specifying the statement in a TRY...CATCH construct.

如果 INSERT 语句违反约束或规则,或者包含与列的数据类型不兼容的值,则该语句将失败,并且返回错误消息。If an INSERT statement violates a constraint or rule, or if it has a value incompatible with the data type of the column, the statement fails and an error message is returned.

如果 INSERT 是使用 SELECT 或 EXECUTE 加载多行,那么一旦加载的值中出现任何违反规则或约束的情况,就会导致终止语句,且不会加载任何行。If INSERT is loading multiple rows with SELECT or EXECUTE, any violation of a rule or constraint that occurs from the values being loaded causes the statement to be stopped, and no rows are loaded.

如果在表达式计算过程中 INSERT 语句遇到算术错误(溢出、被零除或域错误),则数据库引擎Database Engine会处理这些错误,就好像 SET ARITHABORT 设置为 ON 一样。When an INSERT statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, the 数据库引擎Database Engine handles these errors as if SET ARITHABORT is set to ON. 停止批处理,并返回一条错误消息。The batch is stopped, and an error message is returned. 如果 SET ARITHABORT 和 SET ANSI_WARNINGS 为 OFF,并且在对表达式求值的过程中 INSERT、DELETE 或 UPDATE 语句遇到算术错误(溢出、被零除或域错误),SQL ServerSQL Server 将插入或更新一个 NULL 值。During expression evaluation when SET ARITHABORT and SET ANSI_WARNINGS are OFF, if an INSERT, DELETE or UPDATE statement encounters an arithmetic error, overflow, divide-by-zero, or a domain error, SQL ServerSQL Server inserts or updates a NULL value. 如果目标列不可为空,则插入或更新操作将失败,用户将收到错误消息。If the target column is not nullable, the insert or update action fails and the user receives an error.

互操作性Interoperability

当为表或视图的 INSERT 操作定义了 INSTEAD OF 触发器时,则执行该触发器而不是 INSERT 语句。When an INSTEAD OF trigger is defined on INSERT actions against a table or view, the trigger executes instead of the INSERT statement. 有关 INSTEAD OF 触发器的详细信息,请参阅 CREATE TRIGGER (Transact-SQL)For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).

限制和局限Limitations and Restrictions

当向远程表中插入值且没有为所有列指定所有值时,用户必须标识将向其中插入指定值的列。When you insert values into remote tables and not all values for all columns are specified, you must identify the columns to which the specified values are to be inserted.

在将 TOP 与 INSERT 结合使用时,被引用行不按任何顺序排列,不能直接在此语句中指定 ORDER BY 子句。When TOP is used with INSERT the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in this statements. 如果需要使用 TOP 来插入按有意义的时间顺序排列的行,您必须同时使用 TOP 和在嵌套 select 语句中指定的 ORDER BY 子句。If you need to use TOP to insert rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause that is specified in a subselect statement. 请参阅本主题后面的“示例”一节。See the Examples section that follows in this topic.

使用 SELECT 和 ORDER BY 填充行的 INSERT 查询保证了标识值的计算方式,但不能保证行的插入顺序。INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted.

在并行数据仓库中,除非另外还指定了 TOP,否则 ORDER BY 子句在 VIEWS、CREATE TABLE AS SELECT、INSERT SELECT、内联函数、派生表、子查询和常见表表达式中无效。In Parallel Data Warehouse, the ORDER BY clause is invalid in VIEWS, CREATE TABLE AS SELECT, INSERT SELECT, inline functions, derived tables, subqueries and common table expressions, unless TOP is also specified.

日志记录行为Logging Behavior

INSERT 语句始终完全记入日志,只有在将 OPENROWSET 函数与 BULK 关键字一起使用或者在使用 INSERT INTO <target_table> SELECT <columns> FROM <source_table> 时除外。The INSERT statement is always fully logged except when using the OPENROWSET function with the BULK keyword or when using INSERT INTO <target_table> SELECT <columns> FROM <source_table>. 这些操作可进行最小日志记录。These operations can be minimally logged. 有关详细信息,请参阅本主题前面的“大容量加载数据的最佳做法”一节。For more information, see the section "Best Practices for Bulk Loading Data" earlier in this topic.

SecuritySecurity

在链接服务器的连接过程中,发送服务器提供登录名和密码以代表自己连接到接收服务器。During a linked server connection, the sending server provides a login name and password to connect to the receiving server on its behalf. 为了使该连接有效,必须使用 sp_addlinkedsrvlogin 在链接服务器之间创建登录名映射。For this connection to work, you must create a login mapping between the linked servers by using sp_addlinkedsrvlogin.

使用 OPENROWSET(BULK…) 时,请务必了解 SQL ServerSQL Server 是如何处理模拟的。When you use OPENROWSET(BULK...), it is important to understand how SQL ServerSQL Server handles impersonation. 有关详细信息,请参阅使用 BULK INSERT 或 OPENROWSET (BULK...) 批量导入数据 (SQL Server) 中的“安全注意事项”。For more information, see "Security Considerations" in Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server).

权限Permissions

需要对目标表具有 INSERT 权限。INSERT permission is required on the target table.

默认情况下,将 INSERT 权限授予 sysadmin 固定服务器角色成员、db_owner 和 db_datawriter 固定数据库角色成员以及表所有者 。INSERT permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. sysadmin、db_owner 和 db_securityadmin 角色成员和表所有者可以将权限转让给其他用户 。Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.

若要使用 OPENROWSET 函数 BULK 选项执行 INSERT,必须是 sysadmin 固定服务器角色成员或 bulkadmin 固定服务器角色成员 。To execute INSERT with the OPENROWSET function BULK option, you must be a member of the sysadmin fixed server role or of the bulkadmin fixed server role.

示例Examples

类别Category 作为特征的语法元素Featured syntax elements
基本语法Basic syntax INSERT • 表值构造函数INSERT • table value constructor
处理列值Handling column values IDENTITY • NEWID • 默认值 • 用户定义类型IDENTITY • NEWID • default values • user-defined types
插入来自其他表的数据Inserting data from other tables INSERT…SELECT • INSERT…EXECUTE • WITH 公用表表达式 • TOP • OFFSET FETCHINSERT...SELECT • INSERT...EXECUTE • WITH common table expression • TOP • OFFSET FETCH
指定目标对象,而非标准表Specifying target objects other than standard tables 视图 • 表变量Views • table variables
向远程表中插入行Inserting rows into a remote table 链接服务器 • OPENQUERY 行集函数 • OPENDATASOURCE 行集函数Linked server • OPENQUERY rowset function • OPENDATASOURCE rowset function
从表或数据文件中大容量加载数据Bulk loading data from tables or data files INSERT…SELECT • OPENROWSET 函数INSERT...SELECT • OPENROWSET function
通过使用提示覆盖查询优化器的默认行为Overriding the default behavior of the query optimizer by using hints 表提示Table hints
捕获 INSERT 语句的结果Capturing the results of the INSERT statement OUTPUT 子句OUTPUT clause

基本语法Basic Syntax

本节中的示例说明了使用最低要求的语法的 INSERT 语句的基本功能。Examples in this section demonstrate the basic functionality of the INSERT statement using the minimum required syntax.

A.A. 插入单行数据Inserting a single row of data

下面的示例在 AdventureWorks2012AdventureWorks2012 数据库的 Production.UnitMeasure 表中插入一行。The following example inserts one row into the Production.UnitMeasure table in the AdventureWorks2012AdventureWorks2012 database. 该表中的各列是 UnitMeasureCodeNameModifiedDateThe columns in this table are UnitMeasureCode, Name, and ModifiedDate. 由于提供了所有列的值并按表中各列的顺序列出这些值,因此不必在列列表中指定列名 Because values for all columns are supplied and are listed in the same order as the columns in the table, the column names do not have to be specified in the column list .

INSERT INTO Production.UnitMeasure  
VALUES (N'FT', N'Feet', '20080414');  

B.B. 插入多行数据Inserting multiple rows of data

下面的示例使用表值构造函数在单个 INSERT 语句中将三行插入 AdventureWorks2012AdventureWorks2012 数据库的 Production.UnitMeasure 表。The following example uses the table value constructor to insert three rows into the Production.UnitMeasure table in the AdventureWorks2012AdventureWorks2012 database in a single INSERT statement. 由于提供了所有列的值并按表中各列的顺序列出这些值,因此不必在列列表中指定列名。Because values for all columns are supplied and are listed in the same order as the columns in the table, the column names do not have to be specified in the column list.

INSERT INTO Production.UnitMeasure  
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923')
    , (N'Y3', N'Cubic Yards', '20080923');  

C.C. 按与表列顺序不同的顺序插入数据Inserting data that is not in the same order as the table columns

下面的示例使用列列表显式指定插入到每个列中的值。The following example uses a column list to explicitly specify the values that are inserted into each column. AdventureWorks2012AdventureWorks2012 数据库的 Production.UnitMeasure 表中的列顺序为 UnitMeasureCodeNameModifiedDate;但这些列的列出顺序与 column_list 中的顺序不同 。The column order in the Production.UnitMeasure table in the AdventureWorks2012AdventureWorks2012 database is UnitMeasureCode, Name, ModifiedDate; however, the columns are not listed in that order in column_list.

INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,  
    ModifiedDate)  
VALUES (N'Square Yards', N'Y2', GETDATE());  

处理列值Handling Column Values

本节中的示例说明将值插入列中的方法,这些列是使用 IDENTITY 属性或 DEFAULT 值定义的列,或者是用 uniqueidentifer 之类的数据类型定义的列,或者是用户定义类型列 。Examples in this section demonstrate methods of inserting values into columns that are defined with an IDENTITY property, DEFAULT value, or are defined with data types such as uniqueidentifer or user-defined type columns.

D.D. 将数据插入其列具有默认值的表Inserting data into a table with columns that have default values

下面的示例演示了如何将行插入到包含自动生成值或具有默认值的列的表中。The following example shows inserting rows into a table with columns that automatically generate a value or have a default value. Column_1 是一个计算列,它通过将一个字符串与插入 column_2 的值进行串联,自动生成一个值。Column_1 is a computed column that automatically generates a value by concatenating a string with the value inserted into column_2. Column_2 是用默认约束定义的。Column_2 is defined with a default constraint. 如果没有为该列指定值,将使用默认值。If a value is not specified for this column, the default value is used. Column_3 是使用 rowversion 数据类型定义的,它自动生成一个唯一的、递增的二进制数字 。Column_3 is defined with the rowversion data type, which automatically generates a unique, incrementing binary number. Column_4 不自动生成值。Column_4 does not automatically generate a value. 如果没有为该列指定值,将插入 NULL。When a value for this column is not specified, NULL is inserted. INSERT 语句插入一些行,这些行只有部分列包含值。The INSERT statements insert rows that contain values for some of the columns but not all. 在最后一个 INSERT 语句中,未指定列,只通过使用 DEFAULT VALUES 子句插入了默认值。In the last INSERT statement, no columns are specified and only the default values are inserted by using the DEFAULT VALUES clause.

CREATE TABLE dbo.T1   
(  
    column_1 AS 'Computed column ' + column_2,   
    column_2 varchar(30)   
        CONSTRAINT default_name DEFAULT ('my column default'),  
    column_3 rowversion,  
    column_4 varchar(40) NULL  
);  
GO  
INSERT INTO dbo.T1 (column_4)   
    VALUES ('Explicit value');  
INSERT INTO dbo.T1 (column_2, column_4)   
    VALUES ('Explicit value', 'Explicit value');  
INSERT INTO dbo.T1 (column_2)   
    VALUES ('Explicit value');  
INSERT INTO T1 DEFAULT VALUES;   
GO  
SELECT column_1, column_2, column_3, column_4  
FROM dbo.T1;  
GO  

E.E. 将数据插入到含标识列的表中Inserting data into a table with an identity column

下面的示例演示了将数据插入到标识列中的不同方法。The following example shows different methods of inserting data into an identity column. 前两个 INSERT 语句允许为新行生成标识值。The first two INSERT statements allow identity values to be generated for the new rows. 第三个 INSERT 语句用 SET IDENTITY_INSERT 语句覆盖列的 IDENTITY 属性,并将一个显式值插入到标识列中。The third INSERT statement overrides the IDENTITY property for the column with the SET IDENTITY_INSERT statement and inserts an explicit value into the identity column.

CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30));  
GO  
INSERT T1 VALUES ('Row #1');  
INSERT T1 (column_2) VALUES ('Row #2');  
GO  
SET IDENTITY_INSERT T1 ON;  
GO  
INSERT INTO T1 (column_1,column_2)   
    VALUES (-99, 'Explicit identity value');  
GO  
SELECT column_1, column_2  
FROM T1;  
GO  

F.F. 通过使用 NEWID() 将数据插入到 uniqueidentifier 列中Inserting data into a uniqueidentifier column by using NEWID()

下面的示例使用 NEWID() 函数获取 column_2 的 GUID。The following example uses the NEWID() function to obtain a GUID for column_2. 与标识列不同,数据库引擎Database Engine 不为 uniqueidentifier 数据类型的列自动生成值(如第二个 INSERT 语句所示)。Unlike for identity columns, the 数据库引擎Database Engine does not automatically generate values for columns with the uniqueidentifier data type, as shown by the second INSERT statement.

CREATE TABLE dbo.T1   
(  
    column_1 int IDENTITY,   
    column_2 uniqueidentifier,  
);  
GO  
INSERT INTO dbo.T1 (column_2)   
    VALUES (NEWID());  
INSERT INTO T1 DEFAULT VALUES;   
GO  
SELECT column_1, column_2  
FROM dbo.T1;  
  

G.G. 将数据插入到用户定义类型列中Inserting data into user-defined type columns

下面的 Transact-SQLTransact-SQL 语句将三行插入到 PointValue 表的 Points 列中。The following Transact-SQLTransact-SQL statements insert three rows into the PointValue column of the Points table. 该列使用 CLR 用户定义类型 (UDT)。This column uses a CLR user-defined type (UDT). Point 数据类型由作为 UDT 属性公开的 X 和 Y 整数值组成。The Point data type consists of X and Y integer values that are exposed as properties of the UDT. 必须使用 CAST 或 CONVERT 函数,才能将以逗号分隔的 X 和 Y 值转换为 Point 类型。You must use either the CAST or CONVERT function to cast the comma-delimited X and Y values to the Point type. 前两个语句使用 CONVERT 函数将字符串值转换为 Point 类型,第三个语句使用 CAST 函数。The first two statements use the CONVERT function to convert a string value to the Point type, and the third statement uses the CAST function. 有关详细信息,请参阅操作 UDT 数据For more information, see Manipulating UDT Data.

INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));  
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));  
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));  

插入来自其他表的数据Inserting Data from Other Tables

本节中的示例说明将行从一个表插入另一个表的方法。Examples in this section demonstrate methods of inserting rows from one table into another table.

H.H. 使用 SELECT 和 EXECUTE 选项插入来自其他表的数据Using the SELECT and EXECUTE options to insert data from other tables

下面的示例说明如何使用 INSERT…SELECT 或 INSERT…EXECUTE 将来自一个表的数据插入另一个表。The following example shows how to insert data from one table into another table by using INSERT...SELECT or INSERT...EXECUTE. 每种方法都基于一个多表 SELECT 语句,该语句在列列表中包含一个表达式及一个文字值。Each is based on a multi-table SELECT statement that includes an expression and a literal value in the column list.

第一个 INSERT 语句使用 SELECT 语句从 AdventureWorks2012AdventureWorks2012 数据库的源表(EmployeeSalesPersonPerson)中派生数据,并将结果集存储在 EmployeeSales 表中。The first INSERT statement uses a SELECT statement to derive the data from the source tables (Employee, SalesPerson, and Person) in the AdventureWorks2012AdventureWorks2012 database and store the result set in the EmployeeSales table. 第二个 INSERT 语句使用 EXECUTE 子句调用包含 SELECT 语句的存储过程,第三个 INSERT 使用 EXECUTE 子句将 SELECT 语句作为文字字符串引用。The second INSERT statement uses the EXECUTE clause to call a stored procedure that contains the SELECT statement, and the third INSERT uses the EXECUTE clause to reference the SELECT statement as a literal string.

CREATE TABLE dbo.EmployeeSales  
( DataSource   varchar(20) NOT NULL,  
  BusinessEntityID   varchar(11) NOT NULL,  
  LastName     varchar(40) NOT NULL,  
  SalesDollars money NOT NULL  
);  
GO  
CREATE PROCEDURE dbo.uspGetEmployeeSales   
AS   
    SET NOCOUNT ON;  
    SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName,   
        sp.SalesYTD   
    FROM Sales.SalesPerson AS sp    
    INNER JOIN Person.Person AS c  
        ON sp.BusinessEntityID = c.BusinessEntityID  
    WHERE sp.BusinessEntityID LIKE '2%'  
    ORDER BY sp.BusinessEntityID, c.LastName;  
GO  
--INSERT...SELECT example  
INSERT INTO dbo.EmployeeSales  
    SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD   
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c  
        ON sp.BusinessEntityID = c.BusinessEntityID  
    WHERE sp.BusinessEntityID LIKE '2%'  
    ORDER BY sp.BusinessEntityID, c.LastName;  
GO  
--INSERT...EXECUTE procedure example  
INSERT INTO dbo.EmployeeSales   
EXECUTE dbo.uspGetEmployeeSales;  
GO  
--INSERT...EXECUTE('string') example  
INSERT INTO dbo.EmployeeSales   
EXECUTE   
('  
SELECT ''EXEC STRING'', sp.BusinessEntityID, c.LastName,   
    sp.SalesYTD   
    FROM Sales.SalesPerson AS sp   
    INNER JOIN Person.Person AS c  
        ON sp.BusinessEntityID = c.BusinessEntityID  
    WHERE sp.BusinessEntityID LIKE ''2%''  
    ORDER BY sp.BusinessEntityID, c.LastName  
');  
GO  
--Show results.  
SELECT DataSource,BusinessEntityID,LastName,SalesDollars  
FROM dbo.EmployeeSales;  

I.I. 使用 WITH 公共表表达式定义插入的数据Using WITH common table expression to define the data inserted

下面的示例在 AdventureWorks2012AdventureWorks2012 数据库中创建 NewEmployee 表。The following example creates the NewEmployee table in the AdventureWorks2012AdventureWorks2012 database. 公用表表达式 (EmployeeTemp) 定义要插入到 NewEmployee 表中的来自一个或多个表的行。A common table expression (EmployeeTemp) defines the rows from one or more tables to be inserted into the NewEmployee table. INSERT 语句引用公用表表达式中的列。The INSERT statement references the columns in the common table expression.

CREATE TABLE HumanResources.NewEmployee  
(  
    EmployeeID int NOT NULL,  
    LastName nvarchar(50) NOT NULL,  
    FirstName nvarchar(50) NOT NULL,  
    PhoneNumber Phone NULL,  
    AddressLine1 nvarchar(60) NOT NULL,  
    City nvarchar(30) NOT NULL,  
    State nchar(3) NOT NULL,   
    PostalCode nvarchar(15) NOT NULL,  
    CurrentFlag Flag  
);  
GO  
WITH EmployeeTemp (EmpID, LastName, FirstName, Phone,   
                   Address, City, StateProvince,   
                   PostalCode, CurrentFlag)  
AS (SELECT   
       e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,  
       a.AddressLine1, a.City, sp.StateProvinceCode,   
       a.PostalCode, e.CurrentFlag  
    FROM HumanResources.Employee e  
        INNER JOIN Person.BusinessEntityAddress AS bea  
        ON e.BusinessEntityID = bea.BusinessEntityID  
        INNER JOIN Person.Address AS a  
        ON bea.AddressID = a.AddressID  
        INNER JOIN Person.PersonPhone AS pp  
        ON e.BusinessEntityID = pp.BusinessEntityID  
        INNER JOIN Person.StateProvince AS sp  
        ON a.StateProvinceID = sp.StateProvinceID  
        INNER JOIN Person.Person as c  
        ON e.BusinessEntityID = c.BusinessEntityID  
    )  
INSERT INTO HumanResources.NewEmployee   
    SELECT EmpID, LastName, FirstName, Phone,   
           Address, City, StateProvince, PostalCode, CurrentFlag  
    FROM EmployeeTemp;  
GO  

J.J. 使用 TOP 限制从源表插入的数据Using TOP to limit the data inserted from the source table

下面的示例创建 EmployeeSales 表,并插入 AdventureWorks2012AdventureWorks2012 数据库的 HumanResources.Employee 表中的前 5 名随机雇员的姓名和本年度到目前为止的销售数据。The following example creates the table EmployeeSales and inserts the name and year-to-date sales data for the top 5 random employees from the table HumanResources.Employee in the AdventureWorks2012AdventureWorks2012 database. INSERT 语句选择 SELECT 语句返回的任意 5 行。The INSERT statement chooses any 5 rows returned by the SELECT statement. OUTPUT 子句将显示插入 EmployeeSales 表中的行。The OUTPUT clause displays the rows that are inserted into the EmployeeSales table. 请注意,SELECT 语句中的 ORDER BY 子句不用于确定前 5 名雇员。Notice that the ORDER BY clause in the SELECT statement is not used to determine the top 5 employees.

CREATE TABLE dbo.EmployeeSales  
( EmployeeID   nvarchar(11) NOT NULL,  
  LastName     nvarchar(20) NOT NULL,  
  FirstName    nvarchar(20) NOT NULL,  
  YearlySales  money NOT NULL  
 );  
GO  
INSERT TOP(5)INTO dbo.EmployeeSales  
    OUTPUT inserted.EmployeeID, inserted.FirstName, 
        inserted.LastName, inserted.YearlySales  
    SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD   
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c  
        ON sp.BusinessEntityID = c.BusinessEntityID  
    WHERE sp.SalesYTD > 250000.00  
    ORDER BY sp.SalesYTD DESC;  

如果必须使用 TOP 来插入按有意义的时间顺序排列的行,您必须同时使用 TOP 和嵌套 select 语句中的 ORDER BY,如以下示例所示。If you have to use TOP to insert rows in a meaningful chronological order, you must use TOP together with ORDER BY in a subselect statement as shown in the following example. OUTPUT 子句将显示插入 EmployeeSales 表中的行。The OUTPUT clause displays the rows that are inserted into the EmployeeSales table. 请注意,现在基于 ORDER BY 子句的结果而非随机行插入前 5 名员工。Notice that the top 5 employees are now inserted based on the results of the ORDER BY clause instead of random rows.

INSERT INTO dbo.EmployeeSales  
    OUTPUT inserted.EmployeeID, inserted.FirstName, 
        inserted.LastName, inserted.YearlySales  
    SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD   
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c  
        ON sp.BusinessEntityID = c.BusinessEntityID  
    WHERE sp.SalesYTD > 250000.00  
    ORDER BY sp.SalesYTD DESC;  

指定目标对象,而非标准表Specifying Target Objects Other Than Standard Tables

本节中的示例说明如何通过指定视图或表变量来插入行。Examples in this section demonstrate how to insert rows by specifying a view or table variable.

K.K. 通过指定视图来插入数据Inserting data by specifying a view

下面的示例将一个视图名指定为目标对象,但将新行插入到基础基表中。The following example specifies a view name as the target object; however, the new row is inserted in the underlying base table. INSERT 语句中值的顺序必须与视图的列顺序相匹配。The order of the values in the INSERT statement must match the column order of the view. 有关详细信息,请参阅通过视图修改数据For more information, see Modify Data Through a View.

CREATE TABLE T1 ( column_1 int, column_2 varchar(30));  
GO  
CREATE VIEW V1 AS   
SELECT column_2, column_1   
FROM T1;  
GO  
INSERT INTO V1   
    VALUES ('Row 1',1);  
GO  
SELECT column_1, column_2   
FROM T1;  
GO  
SELECT column_1, column_2  
FROM V1;  
GO  

L.L. 向表变量中插入数据Inserting data into a table variable

下面的示例将一个表变量指定为 AdventureWorks2012AdventureWorks2012 数据库中的目标对象。The following example specifies a table variable as the target object in the AdventureWorks2012AdventureWorks2012 database.

-- Create the table variable.  
DECLARE @MyTableVar table(  
    LocationID int NOT NULL,  
    CostRate smallmoney NOT NULL,  
    NewCostRate AS CostRate * 1.5,  
    ModifiedDate datetime);  
  
-- Insert values into the table variable.  
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)  
    SELECT LocationID, CostRate, GETDATE() 
    FROM Production.Location  
    WHERE CostRate > 0;  
  
-- View the table variable result set.  
SELECT * FROM @MyTableVar;  
GO  

向远程表中插入行Inserting Rows into a Remote Table

本节中的示例说明如何通过使用链接服务器行集函数引用一个远程目标表,向该表插入行。Examples in this section demonstrate how to insert rows into a remote target table by using a linked server or a rowset function to reference the remote table.

M.M. 通过使用链接服务器向远程表插入数据Inserting data into a remote table by using a linked server

下面的示例将行插入一个远程表中。The following example inserts rows into a remote table. 该示例从使用 sp_addlinkedserver 创建指向远程数据源的链接开始。The example begins by creating a link to the remote data source by using sp_addlinkedserver. 然后,将链接服务器名称 MyLinkServer 指定为 server.catalog.schema.object 形式的由四个部分组成的对象名称的一部分 。The linked server name, MyLinkServer, is then specified as part of the four-part object name in the form server.catalog.schema.object.

适用于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_nameinstance_name'.  
  
EXEC sp_addlinkedserver @server = N'MyLinkServer',  
    @srvproduct = N' ',  
    @provider = N'SQLNCLI',   
    @datasrc = N'server_name',  
    @catalog = N'AdventureWorks2012';  
GO  
-- Specify the remote data source in the FROM clause using a four-part name   
-- in the form linked_server.catalog.schema.object.  
  
INSERT INTO MyLinkServer.AdventureWorks2012.HumanResources.Department (Name, GroupName)  
VALUES (N'Public Relations', N'Executive General and Administration');  
GO  

N.N. 通过使用 OPENQUERY 函数向远程表插入数据Inserting data into a remote table by using the OPENQUERY function

下面的示例通过指定 OPENQUERY 行集函数向远程表插入一行。The following example inserts a row into a remote table by specifying the OPENQUERY rowset function. 在之前例子中创建的链接服务器名称用于此示例。The linked server name created in the previous example is used in this example.

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

INSERT OPENQUERY (MyLinkServer, 
    'SELECT Name, GroupName 
     FROM AdventureWorks2012.HumanResources.Department')  
VALUES ('Environmental Impact', 'Engineering');  
GO  

O.O. 通过使用 OPENDATASOURCE 函数向远程表插入数据Inserting data into a remote table by using the OPENDATASOURCE function

下面的示例通过指定 OPENDATASOURCE 行集函数向远程表插入一行。The following example inserts a row into a remote table by specifying the OPENDATASOURCE rowset function. 通过使用 server_name 或 server_name\instance_name 格式,为该数据源指定一个有效的服务器名称 。Specify a valid server name for the data source by using the format server_name or server_name\instance_name.

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

-- 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_nameinstance_name.  
  
INSERT INTO OPENDATASOURCE('SQLNCLI',  
    'Data Source= <server_name>; Integrated Security=SSPI')  
    .AdventureWorks2012.HumanResources.Department (Name, GroupName)  
    VALUES (N'Standards and Methods', 'Quality Assurance');  
GO  

P.P. 插入到使用 PolyBase 创建的外部表中Inserting into an external table created using PolyBase

将数据从 SQL Server 导出到 Hadoop 或 Azure 存储空间。Export data from SQL Server to Hadoop or Azure Storage. 首先,创建一个指向目标文件或目录的外部表。First, create an external table that points to the destination file or directory. 然后,使用 INSERT INTO 将数据从本地 SQL Server 表导出到外部数据源。Then, use INSERT INTO to export data from a local SQL Server table to an external data source. INSERT INTO 语句将创建目标文件或目录(如果不存在),而 SELECT 语句的结果将以指定文件格式导出到指定位置。The INSERT INTO statement creates the destination file or directory if it does not exist and the results of the SELECT statement are exported to the specified location in the specified file format. 有关详细信息,请参阅 PolyBase 入门For more information, see Get started with PolyBase.

适用于SQL ServerSQL ServerApplies to: SQL ServerSQL Server.

-- Create an external table.   
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] (  
        [FirstName] char(25) NOT NULL,   
        [LastName] char(25) NOT NULL,   
        [YearlyIncome] float NULL,   
        [MaritalStatus] char(1) NOT NULL  
)  
WITH (  
        LOCATION='/old_data/2009/customerdata.tbl',  
        DATA_SOURCE = HadoopHDP2,  
        FILE_FORMAT = TextFileFormat,  
        REJECT_TYPE = VALUE,  
        REJECT_VALUE = 0  
);  
  
-- Export data: Move old data to Hadoop while keeping 
-- it query-able via external table.  

INSERT INTO dbo.FastCustomer2009  
SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2  
ON (T1.CustomerKey = T2.CustomerKey)  
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;  

从表或数据文件中大容量加载数据Bulk Loading Data from Tables or Data Files

本节中的示例说明通过 INSERT 语句向表中大容量加载数据的两个方法。Examples in this section demonstrate two methods to bulk load data into a table by using the INSERT statement.

Q.Q. 将数据插入堆中并按最小方式记录日志Inserting data into a heap with minimal logging

下面的示例创建一个新表(一个堆),并使用最小方式记录日志将来自其他表中的数据插入到这个新表中。The following example creates a new table (a heap) and inserts data from another table into it using minimal logging. 此示例假定 AdventureWorks2012 数据库的恢复模式设置为 FULL。The example assumes that the recovery model of the AdventureWorks2012 database is set to FULL. 若要确保使用最小方式记录,应在插入行之前将 AdventureWorks2012 数据库的恢复模式设置为 BULK_LOGGED,并在 INSERT INTO…SELECT 语句后重置为 FULL。To ensure minimal logging is used, the recovery model of the AdventureWorks2012 database is set to BULK_LOGGED before rows are inserted and reset to FULL after the INSERT INTO...SELECT statement. 此外,为目标表 Sales.SalesHistory 指定了 TABLOCK 提示。In addition, the TABLOCK hint is specified for the target table Sales.SalesHistory. 这确保语句在事务日志中占用最少的空间并且高效执行。This ensures that the statement uses minimal space in the transaction log and performs efficiently.

-- Create the target heap.  
CREATE TABLE Sales.SalesHistory(  
    SalesOrderID int NOT NULL,  
    SalesOrderDetailID int NOT NULL,  
    CarrierTrackingNumber nvarchar(25) NULL,  
    OrderQty smallint NOT NULL,  
    ProductID int NOT NULL,  
    SpecialOfferID int NOT NULL,  
    UnitPrice money NOT NULL,  
    UnitPriceDiscount money NOT NULL,  
    LineTotal money NOT NULL,  
    rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,  
    ModifiedDate datetime NOT NULL );  
GO  
-- Temporarily set the recovery model to BULK_LOGGED.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY BULK_LOGGED;  
GO  
-- Transfer data from Sales.SalesOrderDetail to Sales.SalesHistory  
INSERT INTO Sales.SalesHistory WITH (TABLOCK)  
    (SalesOrderID,   
     SalesOrderDetailID,  
     CarrierTrackingNumber,   
     OrderQty,   
     ProductID,   
     SpecialOfferID,   
     UnitPrice,   
     UnitPriceDiscount,  
     LineTotal,   
     rowguid,   
     ModifiedDate)  
SELECT * FROM Sales.SalesOrderDetail;  
GO  
-- Reset the recovery model.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY FULL;  
GO  

R.R. 将 OPENROWSET 函数与 BULK 一起使用来将数据大容量加载到表中Using the OPENROWSET function with BULK to bulk load data into a table

下面的示例通过指定 OPENROWSET 函数,将来自数据文件的行插入表中。The following example inserts rows from a data file into a table by specifying the OPENROWSET function. 出于性能优化目的,指定了 IGNORE_TRIGGERS 表提示。The IGNORE_TRIGGERS table hint is specified for performance optimization. 若要查看更多示例,请参阅使用 BULK INSERT 或 OPENROWSET (BULK...) 导入批量数据 (SQL Server)For more examples, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server).

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

INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)  
SELECT b.Name, b.GroupName   
FROM OPENROWSET (  
    BULK 'C:SQLFilesDepartmentData.txt',  
    FORMATFILE = 'C:SQLFilesBulkloadFormatFile.xml',  
    ROWS_PER_BATCH = 15000)AS b ;  

通过使用提示覆盖查询优化器的默认行为Overriding the Default Behavior of the Query Optimizer by Using Hints

本节中的示例说明如何使用表提示在处理 INSERT 语句时暂时覆盖查询优化器的默认行为。Examples in this section demonstrate how to use table hints to temporarily override the default behavior of the query optimizer when processing the INSERT statement.

注意

由于 SQL ServerSQL Server 查询优化器通常会为查询选择最佳执行计划,因此我们建议仅在最后迫不得已的情况下才可由资深的开发人员和数据库管理员使用提示。Because the SQL ServerSQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.

S.S. 使用 TABLOCK 提示指定锁定方法Using the TABLOCK hint to specify a locking method

下面的示例指定对 Production.Location 表采用排他 (X) 锁,并保持到 INSERT 语句结束。The following example specifies that an exclusive (X) lock is taken on the Production.Location table and is held until the end of the INSERT statement.

适用范围:SQL ServerSQL ServerSQL 数据库SQL DatabaseApplies to: SQL ServerSQL Server, SQL 数据库SQL Database.

INSERT INTO Production.Location WITH (XLOCK)  
(Name, CostRate, Availability)  
VALUES ( N'Final Inventory', 15.00, 80.00);  

捕获 INSERT 语句的结果Capturing the Results of the INSERT Statement

本节中的示例说明如何使用 OUTPUT Clause 从 INSERT 语句影响的每一行返回信息(或基于的表达式)。Examples in this section demonstrate how to use the OUTPUT Clause to return information from, or expressions based on, each row affected by an INSERT statement. 这些结果可以返回到处理应用程序,以供在确认消息、存档以及其他类似的应用程序要求中使用。These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements.

T.T. 将 OUTPUT 用于 INSERT 语句Using OUTPUT with an INSERT statement

下面的示例将行插入到 ScrapReason 表中,并使用 OUTPUT 子句将语句的结果返回到 @MyTableVar 表变量。The following example inserts a row into the ScrapReason table and uses the OUTPUT clause to return the results of the statement to the @MyTableVar table variable. 由于 ScrapReasonID 列使用 IDENTITY 属性定义,因此未在 INSERT 语句中为该列指定值。Because the ScrapReasonID column is defined with an IDENTITY property, a value is not specified in the INSERT statement for that column. 但应注意,数据库引擎Database Engine为该列生成的值在 OUTPUT 列中的 INSERTED.ScrapReasonID 子句中返回。However, note that the value generated by the 数据库引擎Database Engine for that column is returned in the OUTPUT clause in the INSERTED.ScrapReasonID column.

DECLARE @MyTableVar table( NewScrapReasonID smallint,  
                           Name varchar(50),  
                           ModifiedDate datetime);  
INSERT Production.ScrapReason  
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate  
        INTO @MyTableVar  
VALUES (N'Operator error', GETDATE());  
  
--Display the result set of the table variable.  
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;  
--Display the result set of the table.  
SELECT ScrapReasonID, Name, ModifiedDate   
FROM Production.ScrapReason;  

U.U. 将 OUTPUT 用于标识列和计算列Using OUTPUT with identity and computed columns

下面的示例创建 EmployeeSales 表,然后使用 INSERT 语句向其中插入若干行,并使用 SELECT 语句从源表中检索数据。The following example creates the EmployeeSales table and then inserts several rows into it using an INSERT statement with a SELECT statement to retrieve data from source tables. EmployeeSales 表包含标识列 (EmployeeID) 和计算列 (ProjectedSales)。The EmployeeSales table contains an identity column (EmployeeID) and a computed column (ProjectedSales). 由于这些值是在插入操作期间由数据库引擎Database Engine生成的,因此不能在 @MyTableVar 中定义上述两列。Because these values are generated by the 数据库引擎Database Engine during the insert operation, neither of these columns can be defined in @MyTableVar.

CREATE TABLE dbo.EmployeeSales  
( EmployeeID   int IDENTITY (1,5)NOT NULL,  
  LastName     nvarchar(20) NOT NULL,  
  FirstName    nvarchar(20) NOT NULL,  
  CurrentSales money NOT NULL,  
  ProjectedSales AS CurrentSales * 1.10   
);  
GO  
DECLARE @MyTableVar table(  
  LastName     nvarchar(20) NOT NULL,  
  FirstName    nvarchar(20) NOT NULL,  
  CurrentSales money NOT NULL  
  );  
  
INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)  
  OUTPUT INSERTED.LastName,   
         INSERTED.FirstName,   
         INSERTED.CurrentSales  
  INTO @MyTableVar  
    SELECT c.LastName, c.FirstName, sp.SalesYTD  
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c  
        ON sp.BusinessEntityID = c.BusinessEntityID  
    WHERE sp.BusinessEntityID LIKE '2%'  
    ORDER BY c.LastName, c.FirstName;  
  
SELECT LastName, FirstName, CurrentSales  
FROM @MyTableVar;  
GO  
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales  
FROM dbo.EmployeeSales;  

V.V. 插入从 OUTPUT 子句返回的数据Inserting data returned from an OUTPUT clause

下面的示例捕获从 MERGE 语句的 OUTPUT 子句返回的数据,并将该数据插入到另一个表中。The following example captures data returned from the OUTPUT clause of a MERGE statement, and inserts that data into another table. MERGE 语句根据在 AdventureWorks2012AdventureWorks2012 数据库的 Quantity 表中处理的订单每天更新 ProductInventory 表的 SalesOrderDetail 列。The MERGE statement updates the Quantity column of the ProductInventory table daily, based on orders that are processed in the SalesOrderDetail table in the AdventureWorks2012AdventureWorks2012 database. 它还删除库存降为 0 的产品所在的行。It also deletes rows for products whose inventories drop to 0. 本示例捕获已删除的行并将这些行插入另一个表 ZeroInventory 中,该表跟踪没有库存的产品。The example captures the rows that are deleted and inserts them into another table, ZeroInventory, which tracks products with no inventory.

--Create ZeroInventory table.  
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);  
GO  
  
INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)  
SELECT ProductID, GETDATE()  
FROM  
(   MERGE Production.ProductInventory AS pi  
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
           JOIN Sales.SalesOrderHeader AS soh  
           ON sod.SalesOrderID = soh.SalesOrderID  
           AND soh.OrderDate = '20070401'  
           GROUP BY ProductID) AS src (ProductID, OrderQty)  
    ON (pi.ProductID = src.ProductID)  
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0  
        THEN DELETE  
    WHEN MATCHED  
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty  
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)  
WHERE Action = 'DELETE';  
IF @@ROWCOUNT = 0  
PRINT 'Warning: No rows were inserted';  
GO  
SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;  

W.W. 使用 SELECT 选项插入数据Inserting data using the SELECT option

以下示例说明如何使用 INSERT 语句通过 SELECT 选项插入多行数据。The following example shows how to insert multiple rows of data using an INSERT statement with a SELECT option. 第一个 INSERT 语句使用 SELECT 语句直接从源表中检索数据,然后将结果集存储在 EmployeeTitles 表中。The first INSERT statement uses a SELECT statement directly to retrieve data from the source table, and then to store the result set in the EmployeeTitles table.

CREATE TABLE EmployeeTitles  
( EmployeeKey   INT NOT NULL,  
  LastName     varchar(40) NOT NULL,  
  Title      varchar(50) NOT NULL  
);  
INSERT INTO EmployeeTitles  
    SELECT EmployeeKey, LastName, Title   
    FROM ssawPDW.dbo.DimEmployee  
    WHERE EndDate IS NULL;  

X.X. 使用 INSERT 语句指定标签Specifying a label with the INSERT statement

以下示例说明了如何通过 INSERT 语句使用标签。The following example shows the use of a label with an INSERT statement.

-- Uses AdventureWorks  
  
INSERT INTO DimCurrency   
VALUES (500, N'C1', N'Currency1')  
OPTION ( LABEL = N'label1' );  

Y.Y. 通过 INSERT 语句使用标签和查询提示Using a label and a query hint with the INSERT statement

此查询显示通过 INSERT 语句使用标签和查询联接提示的基本语法。This query shows the basic syntax for using a label and a query join hint with the INSERT statement. 将查询提交到控制节点后,运行在计算节点上的 SQL ServerSQL Server 将在生成 SQL ServerSQL Server 查询计划时应用哈希联接策略。After the query is submitted to the Control node, SQL ServerSQL Server, running on the Compute nodes, will apply the hash join strategy when it generates the SQL ServerSQL Server query plan. 有关联接提示以及如何使用 OPTION 子句的详细信息,请参阅 OPTION (SQL Server PDW)For more information on join hints and how to use the OPTION clause, see OPTION (SQL Server PDW).

-- Uses AdventureWorks  
  
INSERT INTO DimCustomer (CustomerKey, CustomerAlternateKey, 
    FirstName, MiddleName, LastName )   
SELECT ProspectiveBuyerKey, ProspectAlternateKey, 
    FirstName, MiddleName, LastName  
FROM ProspectiveBuyer p JOIN DimGeography g ON p.PostalCode = g.PostalCode  
WHERE g.CountryRegionCode = 'FR'  
OPTION ( LABEL = 'Add French Prospects', HASH JOIN);  

另请参阅See Also

BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
FROM (Transact-SQL) FROM (Transact-SQL)
IDENTITY(属性)(Transact-SQL) IDENTITY (Property) (Transact-SQL)
NEWID (Transact-SQL) NEWID (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
UPDATE (Transact-SQL) UPDATE (Transact-SQL)
MERGE (Transact-SQL) MERGE (Transact-SQL)
OUTPUT 子句 (Transact-SQL) OUTPUT Clause (Transact-SQL)
使用插入的和删除的表Use the inserted and deleted Tables