项目
04/01/2012

INSERT (Transact-SQL)

将一个或更多新行添加到 SQL Server 2008 R2 的表或视图中。有关示例，请参阅 INSERT 示例 (Transact-SQL)。

语法

-- Standard INSERT syntax
[ 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 
        }
    }
}
[; ]

-- Syntax for external tool only
INSERT 
{
    [BULK]
    [ database_name . [ schema_name ] . | schema_name . ]
    [ table_name | 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 ]
           ) ]
}
[; ]

<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 ] ) ]

<column_definition> ::=
 column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

<data type> ::= 
[ type_schema_name . ] type_name 
    [ ( precision [ , scale ] | max ]

参数

WITH <common_table_expression>
指定在 INSERT 语句作用域内定义的临时命名结果集（也称为公用表表达式）。结果集源自 SELECT 语句。

公用表表达式 (CTE) 还可以与 SELECT、DELETE、UPDATE、MERGE 和 CREATE VIEW 语句一起使用。有关详细信息，请参阅 WITH common_table_expression (Transact-SQL)。
TOP (expression) [ PERCENT ]
指定将插入的随机行的数目或百分比。expression 可以是行数或行的百分比。在和 INSERT、UPDATE 或 DELETE 语句结合使用的 TOP 表达式中引用的行不按任何顺序排列。

在 INSERT、UPDATE 和 DELETE 语句中，需要使用括号分隔 TOP 中的 expression。有关详细信息，请参阅 TOP (Transact-SQL)。
INTO
一个可选的关键字，可以将它用在 INSERT 和目标表之间。
server_name
表或视图所在的链接服务器的名称。server_name 可以指定为链接服务器名称，或者通过使用 OPENDATASOURCE 函数指定。

在 server_name 指定为某一链接服务器时，需要 database_name 和 schema_name。如果使用 OPENDATASOURCE 指定 server_name，则 database_name 和 schema_name 可能不适用于所有数据源，并且受到访问远程对象的 OLE DB 访问接口的性能的限制。有关详细信息，请参阅分布式查询。
database_name
数据库的名称。
schema_name
该表或视图所属架构的名称。
table_or view_name
要接收数据的表或视图的名称。

表变量在其作用域内可用作 INSERT 语句中的表源。

table_or_view_name 引用的视图必须可更新，并且只在该视图的 FROM 子句中引用一个基表。例如，多表视图中的 INSERT 必须使用只引用一个基表中的各列的 column_list。有关可更新视图的详细信息，请参阅 CREATE VIEW (Transact-SQL)。
rowset_function_limited
OPENQUERY 或 OPENROWSET 函数。使用这些函数受到访问远程对象的 OLE DB 访问接口的性能的限制。有关详细信息，请参阅分布式查询。

WITH ( <table_hint_limited> [...n ] )
指定目标表允许的一个或多个表提示。需要有 WITH 关键字和括号。

不允许 READPAST、NOLOCK 和 READUNCOMMITTED。有关表提示的详细信息，请参阅表提示 (Transact-SQL)。

重要提示
在将来的 SQL Server 版本中，将删除对作为 INSERT 语句目标的表指定 HOLDLOCK、SERIALIZABLE、READCOMMITTED、REPEATABLEREAD 或 UPDLOCK 提示的功能。这些提示不影响 INSERT 语句的性能。请避免在新的开发工作中使用该功能，并计划修改当前使用该功能的应用程序。

对作为 INSERT 语句目标的表指定 TABLOCK 提示与指定 TABLOCKX 提示具有相同的效果。对表采用排他锁。

(column_list)
要在其中插入数据的一列或多列的列表。必须用括号将 column_list 括起来，并且用逗号进行分隔。

如果某列不在 column_list 中，则数据库引擎必须能够基于该列的定义提供一个值；否则不能加载行。如果列满足下面的条件，则数据库引擎将自动为列提供值：
- 具有 IDENTITY 属性。使用下一个增量标识值。
- 有默认值。使用列的默认值。
- 具有 timestamp 数据类型。使用当前的时间戳值。
- 可以为 Null。使用 Null 值。
- 是计算列。使用计算值。
当向标识列中插入显式值时，必须使用 column_list 和值列表，并且表的 SET IDENTITY_INSERT 选项必须为 ON。
OUTPUT 子句
将插入行作为插入操作的一部分返回。结果可返回到处理应用程序或插入到表或表变量中以供进一步处理。

引用本地分区视图、分布式分区视图或远程表的 DML 语句或包含 execute_statement 的 INSERT 语句都不支持 OUTPUT 子句。在包含 <dml_table_source> 子句的 INSERT 语句中不支持 OUTPUT INTO 子句。
VALUES
引入要插入的数据值的一个或多个列表。对于 column_list（如果已指定）或表中的每个列，都必须有一个数据值。必须用圆括号将值列表括起来。

如果值列表中的各值与表中各列的顺序不相同，或者未包含表中各列的值，则必须使用 column_list 显式指定存储每个传入值的列。

您可以使用 Transact-SQL 行构造函数（又称为表值构造函数）在一个 INSERT 语句中指定多个行。行构造函数包含一个 VALUES 子句和多个括在圆括号中且以逗号分隔的值列表。有关详细信息，请参阅表值构造函数 (Transact-SQL)。
DEFAULT
强制数据库引擎加载为列定义的默认值。如果某列并不存在默认值，并且该列允许 Null 值，则插入 NULL。对于使用 timestamp 数据类型定义的列，插入下一个时间戳值。DEFAULT 对标识列无效。
expression
一个常量、变量或表达式。表达式不能包含 EXECUTE 语句。

当引用 Unicode 字符数据类型 nchar、nvarchar 和 ntext 时，“expression”应采用大写字母“N”作为前缀。如果未指定“N”，则 SQL Server 会将字符串转换为与数据库或列的默认排序规则相对应的代码页。此代码页中没有的字符都将丢失。有关详细信息，请参阅使用 Unicode 的服务器端编程。
derived_table
任何有效的 SELECT 语句，它返回将加载到表中的数据行。SELECT 语句不能包含公用表表达式 (CTE)。
execute_statement
任何有效的 EXECUTE 语句，它使用 SELECT 或 READTEXT 语句返回数据。

如果 execute_statement 使用 INSERT，则每个结果集必须与表或 column_list 中的列兼容。

可以使用 execute_statement 对同一服务器或远程服务器执行存储过程。执行远程服务器中的过程，并将结果集返回到本地服务器并加载到本地服务器的表中。在分布式事务中，当连接启用了多个活动结果集 (MARS) 时，无法针对环回链接服务器发出 execute_statement。

如果 execute_statement 使用 READTEXT 语句返回数据，则每个 READTEXT 语句最多可以返回 1 MB (1024 KB) 的数据。execute_statement 还可以用于扩展过程。execute_statement 插入由扩展过程的主线程返回的数据，但不插入主线程以外的线程的输出。

不能将表值参数指定为 INSERT EXEC 语句的目标；但是，可以将它指定为 INSERT EXEC 字符串或存储过程中的源。有关详细信息，请参阅表值参数（数据库引擎）。
<dml_table_source>
指定插入目标表的行是 INSERT、UPDATE、DELETE 或 MERGE 语句的 OUTPUT 子句返回的行；可以通过 WHERE 子句对行进行筛选。如果指定了 <dml_table_source>，外部 INSERT 语句的目标必须满足以下限制：
- 必须是基表而不是视图。
- 不能是远程表。
- 不能对其定义任何触发器。
- 不能参与任何主键-外键关系。
- 不能参与合并复制或事务复制的可更新订阅。
数据库的兼容级别必须设置为 100 或更高。有关详细信息，请参阅 OUTPUT 子句 (Transact-SQL)。
<select_list>
指定要插入 OUTPUT 子句所返回的列的逗号分隔列表。<select_list> 中的列必须与要插入值的列兼容。<select_list> 无法引用聚合函数或 TEXTPTR。

注意

无论在 <dml_statement_with_output_clause> 中对 SELECT 列表中列出的任何变量做何种更改，这些变量都将引用其原始值。
<dml_statement_with_output_clause>
在 OUTPUT 子句中返回受影响行的有效 INSERT、UPDATE、DELETE 或 MERGE 语句。语句中不能包含 WITH 子句，且不能以远程表或分区视图为目标。如果指定了 UPDATE 或 DELETE，则所指定的 UPDATE 或 DELETE 不能是基于游标的。源行不能作为嵌套的 DML 语句进行引用。
WHERE <search_condition>
任意 WHERE 子句，其中包含对 <dml_statement_with_output_clause> 返回的行进行筛选的有效 <search_condition>。有关详细信息，请参阅搜索条件 (Transact-SQL)。在此上下文中使用时，<search_condition> 不能包含子查询、执行数据访问的标量用户定义函数、聚合函数、TEXTPTR 或全文搜索谓词。
DEFAULT VALUES
强制新行包含为每个列定义的默认值。
BULK
由外部工具用来上载二进制数据流。该选项并不旨在用于 SQL Server Management Studio、SQLCMD、OSQL 之类的工具或者 SQL Server Native Client 之类的数据访问应用程序编程接口。
FIRE_TRIGGERS
指定将在二进制数据流上载操作期间执行目标表中定义的所有插入触发器。有关详细信息，请参阅 BULK INSERT (Transact-SQL)。
CHECK_CONSTRAINTS
指定在二进制数据流上载操作期间，必须检查所有对目标表或视图的约束。有关详细信息，请参阅 BULK INSERT (Transact-SQL)。
KEEPNULLS
指定在二进制数据流上载操作期间空列应该保留 null 值。有关详细信息，请参阅在大容量导入期间保留 Null 值或使用默认值。
KILOBYTES_PER_BATCH = kilobytes_per_batch
将每个批处理中数据的近似千字节数 (KB) 指定为 kilobytes_per_batch。有关详细信息，请参阅 BULK INSERT (Transact-SQL)。
ROWS_PER_BATCH =rows_per_batch
指示二进制数据流中近似的数据行数量。有关详细信息，请参阅 BULK INSERT (Transact-SQL)。

注意如果未提供列列表，则引发一个语法错误。

注意
无论在 <dml_statement_with_output_clause> 中对 SELECT 列表中列出的任何变量做何种更改，这些变量都将引用其原始值。

最佳实践

使用 @@ROWCOUNT 函数返回插入到客户端的行数。有关详细信息，请参阅 @@ROWCOUNT (Transact-SQL)。

大容量导入数据的最佳做法

使用 INSERT INTO…SELECT 进行大容量导入数据并按最小方式记录日志

您可以使用 INSERT INTO <target_table> SELECT <columns> FROM <source_table> 高效地将大量行从一个表（例如临时表）传输到按最小方式记录日志的其他表中。按最小方式记录日志可以提高语句的性能，减少在事务期间此操作填充可用事务日志空间的可能性。

针对此语句的按最小方式记录日志具有以下要求：

数据库的恢复模式设置为简单或大容量日志模式。
目标表是空或非空堆。
复制操作未使用目标表。
为目标表指定了 TABLOCK 提示。

此外，可能还可以以最小方式记录通过 MERGE 语句中的插入操作插入堆中的行。

与持有较少限制性大容量更新锁的 BULK INSERT 语句不同，具有 TABLOCK 提示的 INSERT INTO SELECT 语句持有一个针对表的独占 (X) 锁。也就是说您不能使用并行插入操作插入行。有关锁的详细信息，请参阅锁模式。

使用 OPENROWSET 和 BULK 大容量导入数据

OPENROWSET 函数可接受以下表提示，这些表提示使用 INSERT 语句提供大容量加载优化：

TABLOCK 提示可以最大限度减少插入操作的日志记录数量。数据库的恢复模式必须设置为简单或大容量日志模式，并且目标表不能用于复制。有关详细信息，请参阅在大容量导入中按最小方式记录日志的前提条件。
IGNORE_CONSTRAINTS 提示可以暂时禁用 FOREIGN KEY 和 CHECK 约束检查。
IGNORE_TRIGGERS 提示可以暂时禁用触发器执行。
KEEPDEFAULTS 提示允许数据记录在某一表列缺少值时插入此列的默认值（如果有），而不是插入 NULL。
KEEPIDENTITY 提示允许导入数据文件中的标识值用于目标表中的标识列。

这些优化类似于可与 BULK INSERT 命令一起使用的优化。有关详细信息，请参阅表提示 (Transact-SQL)。

数据类型

插入行时，考虑以下数据类型行为：

如果将值加载到 char、varchar 或 varbinary 数据类型的列中，则尾随空格（对于 char 和 varchar 为空格，对于 varbinary 为零）的填充或截断由创建表时为该列定义的 SET ANSI_PADDING 设置确定。有关详细信息，请参阅 SET ANSI_PADDING (Transact-SQL)。

下表显示了 SET ANSI_PADDING OFF 的默认操作。

数据类型	默认操作
char	将带有空格的值填充到已定义的列宽。
varchar	删除最后的非空格字符后面的尾随空格，而对于只由空格组成的字符串，一直删除到只留下一个空格。
varbinary	删除尾随的零。

如果将一个空字符串 (' ') 加载到数据类型为 varchar 或 text 的列，则默认操作是加载一个零长度的字符串。
将 Null 值插入到 text 或 image 列不创建有效的文本指针，也不预分配 8 KB 的文本页。有关插入 text 和 image 数据的详细信息，请参阅使用 text、ntext 和 image 函数。
使用 uniqueidentifier 数据类型创建的列存储特殊格式的 16 字节二进制值。与标识列不同，数据库引擎不为 uniqueidentifier 数据类型的列自动生成值。在插入操作过程中，可以将 uniqueidentifier 数据类型的变量和 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 格式的字符串常量（包括连字符在内共 36 个字符，其中 x 表示从 0 到 9 或从 a 到 f 的十六进制数字）用于 uniqueidentifier 列。例如，6F9619FF-8B86-D011-B42D-00C04FC964FF 是 uniqueidentifier 变量或列的有效值。使用 NEWID() 函数获取全局唯一 ID (GUID)。

将值插入到用户定义类型列中

可以通过以下方法将值插入到用户定义的类型列中：

提供用户定义类型的值。
提供 SQL Server 系统数据类型的值，条件是该用户定义类型支持该类型的隐式转换或显式转换。下面的示例演示了如何基于字符串进行显式转换将值插入到用户定义的类型 Point 的列中。
```
INSERT INTO Cities (Location)
VALUES ( CONVERT(Point, '12.3:46.2') );
```
由于所有用户定义的类型可以从二进制值进行隐式转换，因此还可以在不执行显式转换的情况下提供二进制值。有关转换和用户定义类型的详细信息，请参阅对用户定义类型执行操作。
调用一个用户定义函数，该函数返回用户定义类型的值。下面的示例使用用户定义函数 CreateNewPoint() 创建一个用户定义类型 Point 的新值，并将该值插入到 Cities 表中。
```
INSERT INTO Cities (Location)
VALUES ( dbo.CreateNewPoint(x, y) );
```

错误处理

您可以通过在 TRY…CATCH 构造函数中指定 INSERT 语句，实现对该语句的错误处理。有关详细信息，请参阅在 Transact-SQL 中使用 TRY...CATCH。

如果 INSERT 语句违反约束或规则，或者包含与列的数据类型不兼容的值，则该语句将失败，并且返回错误消息。

如果 INSERT 是使用 SELECT 或 EXECUTE 加载多行，那么一旦加载的值中出现任何违反规则或约束的情况，就会导致终止语句，且不会加载任何行。

如果在表达式计算过程中 INSERT 语句遇到算术错误（溢出、被零除或域错误），则数据库引擎会处理这些错误，就好像 SET ARITHABORT 设置为 ON 一样。停止批处理，并返回一条错误消息。如果 SET ARITHABORT 和 SET ANSI_WARNINGS 为 OFF，并且在对表达式求值的过程中 INSERT、DELETE 或 UPDATE 语句遇到算术错误（溢出、被零除或域错误），SQL Server 将插入或更新一个 NULL 值。如果目标列不可为 Null，则插入或更新操作将失败，用户将收到错误消息。有关详细信息，请参阅 ARITHABORT 和 ARITHIGNORE 设置为 ON 时的行为。

互操作性

当为表或视图的 INSERT 操作定义了 INSTEAD OF 触发器时，则执行该触发器而不是 INSERT 语句。有关 INSTEAD OF 触发器的详细信息，请参阅 CREATE TRIGGER (Transact-SQL)。

限制和局限

当向远程表中插入值且没有为所有列指定所有值时，用户必须标识将向其中插入指定值的列。

在本地和远程分区视图上，忽略 INSERT 语句的 SET ROWCOUNT 选项的设置。此外，对于针对远程表发出的 INSERT 语句也不支持此选项。

重要提示
在 SQL Server 的将来版本中，使用 SET ROWCOUNT 将不会影响 DELETE、INSERT 和 UPDATE 语句。请不要在新的开发工作中将 SET ROWCOUNT 与 DELETE、INSERT 和 UPDATE 语句一起使用，并准备修改当前使用它的应用程序。建议您改用 TOP 子句。

锁定行为

INSERT 语句总是在其修改的表上获取排他 (X) 锁并在事务完成之前持有该锁。使用排他锁（X 锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。有关详细信息，请参阅数据库引擎中的锁定。

日志记录行为

INSERT 语句始终完全记入日志，只有在将 OPENROWSET 函数与 BULK 关键字一起使用或者在使用 INSERT INTO <target_table> SELECT <columns> FROM <source_table> 时除外。这些操作可进行最小日志记录。有关详细信息，请参阅本主题前面的“大容量加载数据的最佳做法”一节。