表值构造函数 (Transact-SQL)Table Value Constructor (Transact-SQL)

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

指定要构建到某一表中的一组行值表达式。Specifies a set of row value expressions to be constructed into a table. Transact-SQLTransact-SQL 表值构造函数允许在单个 DML 语句中指定多行数据。The Transact-SQLTransact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. 表值构造函数可以指定为 INSERT VALUES 语句的 VALUES 子句...或指定为 MERGE 语句 USING 子句中的或 FROM 子句中的派生表。The table value constructor can be specified either as the VALUES clause of an INSERT ... VALUES statement, or as a derived table in either the USING clause of the MERGE statement or the FROM clause.

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

语法Syntax

  
VALUES ( <row value expression list> ) [ ,...n ]   
  
<row value expression list> ::=  
    {<row value expression> } [ ,...n ]  
  
<row value expression> ::=  
    { DEFAULT | NULL | expression }  

参数Arguments

VALUESVALUES
介绍行值表达式列表。Introduces the row value expression lists. 每个列表都必须用括号括起来并由逗号分隔。Each list must be enclosed in parentheses and separated by a comma.

在每个列表中指定的值的数目必须相同,并且值必须采用与表中的列相同的顺序。The number of values specified in each list must be the same and the values must be in the same order as the columns in the table. 表中每个列的值必须指定,或者列列表必须显式为每个传入值指定列。A value for each column in the table must be specified or the column list must explicitly specify the columns for each incoming value.

DEFAULTDEFAULT
强制数据库引擎Database Engine插入为列定义的默认值。Forces the 数据库引擎Database Engine to insert 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. DEFAULT 对标识列无效。DEFAULT is not valid for an identity column. 当在表值构造函数中指定时,只在 INSERT 语句中允许 DEFAULT。When specified in a table value constructor, DEFAULT is allowed only in an INSERT statement.

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

限制和局限Limitations and Restrictions

当指定为派生表时,行数没有限制。When used as a derived table, there is no limit to the number of rows.

当用作 INSERT VALUES 语句的 VALUES 子句时......限制为最多 1000行。When used as the VALUES clause of an INSERT ... VALUES statement, there is a limit of 1000 rows. 如果行数超过最大值,则返回错误 10738。Error 10738 is returned if the number of rows exceeds the maximum. 若要插入超过 1000 行的数据,请使用下列方法之一:To insert more than 1000 rows, use one of the following methods:

只允许单个标量值作为行值表达式。Only single scalar values are allowed as a row value expression. 涉及多列的子查询不允许作为行值表达式。A subquery that involves multiple columns is not allowed as a row value expression. 例如,以下代码导致语法错误,因为第三个行值表达式列表包含具有多列的子查询。For example, the following code results in a syntax error because the third row value expression list contains a subquery with multiple columns.

USE AdventureWorks2012;  
GO  
CREATE TABLE dbo.MyProducts (Name varchar(50), ListPrice money);  
GO  
-- This statement fails because the third values list contains multiple columns in the subquery.  
INSERT INTO dbo.MyProducts (Name, ListPrice)  
VALUES ('Helmet', 25.50),  
       ('Wheel', 30.00),  
       (SELECT Name, ListPrice FROM Production.Product WHERE ProductID = 720);  
GO  

但是,可以通过单独在子查询中指定每一列,重新编写该语句。However, the statement can be rewritten by specifying each column in the subquery separately. 下面的示例成功地将三行插入 MyProducts 表中。The following example successfully inserts three rows into the MyProducts table.

INSERT INTO dbo.MyProducts (Name, ListPrice)  
VALUES ('Helmet', 25.50),  
       ('Wheel', 30.00),  
       ((SELECT Name FROM Production.Product WHERE ProductID = 720),  
        (SELECT ListPrice FROM Production.Product WHERE ProductID = 720));  
GO  

数据类型Data Types

在多行 INSERT 语句中指定的值遵循 UNION ALL 语法的数据类型约定属性。The values specified in a multi-row INSERT statement follow the data type conversion properties of the UNION ALL syntax. 这会导致不匹配类型隐式转换到更高优先级的类型。This results in the implicit conversion of unmatched types to the type of higher precedence. 如果此转换不是所支持的隐式转换,则返回错误。If the conversion is not a supported implicit conversion, an error is returned. 例如,以下语句将整数值和字符值插入到类型为 char 的列中 。For example, the following statement inserts an integer value and a character value into a column of type char.

CREATE TABLE dbo.t (a int, b char);  
GO  
INSERT INTO dbo.t VALUES (1,'a'), (2, 1);  
GO  

运行 INSERT 语句时,SQL ServerSQL Server 尝试将 'a' 转换为整数,因为数据类型优先级指示整数类型的优先级高于字符。When the INSERT statement is run, SQL ServerSQL Server tries to convert 'a' to an integer because the data type precedence indicates that an integer is of a higher type than a character. 转换失败,并且返回错误。The conversion fails and an error is returned. 您可以根据需要显式转换值,从而避免发生此错误。You can avoid the error by explicitly converting values as appropriate. 例如,前面的语句可以编写为:For example, the previous statement can be written as follows.

INSERT INTO dbo.t VALUES (1,'a'), (2, CONVERT(CHAR,1));  

示例Examples

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

下面的示例创建表 dbo.Departments,然后使用表值构造函数将五行数据插入到该表中。The following example creates the table dbo.Departments and then uses the table value constructor to insert five rows into the table. 由于提供了所有列的值并按表中各列的顺序列出这些值,因此不必在列列表中指定列名。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.

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

B.B. 使用 DEFAULT 和 NULL 值插入多行Inserting multiple rows with DEFAULT and NULL values

下面的示例说明如何在使用表值构造函数向表中插入行时指定 DEFAULT 和 NULL。The following example demonstrates specifying DEFAULT and NULL when using the table value constructor to insert rows into a table.

USE AdventureWorks2012;  
GO  
CREATE TABLE Sales.MySalesReason(  
SalesReasonID int IDENTITY(1,1) NOT NULL,  
Name dbo.Name NULL ,  
ReasonType dbo.Name NOT NULL DEFAULT 'Not Applicable' );  
GO  
INSERT INTO Sales.MySalesReason   
VALUES ('Recommendation','Other'), ('Advertisement', DEFAULT), (NULL, 'Promotion');  
  
SELECT * FROM Sales.MySalesReason;  

C.C. 在 FROM 子句中将多个值指定为派生表Specifying multiple values as a derived table in a FROM clause

下面的示例在 SELECT 语句的 FROM 子句中使用表值构造函数指定多个值。The following examples use the table value constructor to specify multiple values in the FROM clause of a SELECT statement.

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);  
GO  
-- Used in an inner join to specify values to return.  
SELECT ProductID, a.Name, Color  
FROM Production.Product AS a  
INNER JOIN (VALUES ('Blade'), ('Crown Race'), ('AWC Logo Cap')) AS b(Name)   
ON a.Name = b.Name;  

D.D. 在 MERGE 语句中将多个值指定为派生源表Specifying multiple values as a derived source table in a MERGE statement

下面的示例使用 MERGE 以更新或插入行的方式来修改 SalesReason 表。The following example uses MERGE to modify the SalesReason table by either updating or inserting rows. 当源表中的 NewName 值与目标表 (Name) 的 SalesReason 列中的值匹配时,就会更新此目标表中的 ReasonType 列。When the value of NewName in the source table matches a value in the Name column of the target table, (SalesReason), the ReasonType column is updated in the target table. NewName 的值不匹配时,就会将源行插入到目标表中。When the value of NewName does not match, the source row is inserted into the target table. 此源表是一个派生表,它使用 Transact-SQLTransact-SQL 表值构造函数指定源表的多个行。The source table is a derived table that uses the Transact-SQLTransact-SQL table value constructor to specify multiple rows for the source table.

USE AdventureWorks2012;  
GO  
-- Create a temporary table variable to hold the output actions.  
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));  
  
MERGE INTO Sales.SalesReason AS Target  
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))  
       AS Source (NewName, NewReasonType)  
ON Target.Name = Source.NewName  
WHEN MATCHED THEN  
UPDATE SET ReasonType = Source.NewReasonType  
WHEN NOT MATCHED BY TARGET THEN  
INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)  
OUTPUT $action INTO @SummaryOfChanges;  
  
-- Query the results of the table variable.  
SELECT Change, COUNT(*) AS CountPerChange  
FROM @SummaryOfChanges  
GROUP BY Change;  

E.E. 插入超过 1000 行Inserting more than 1000 rows

以下示例演示如何将表值构造函数用作派生表。The following example demonstrates using the table value constructor as a derived table. 此方式可从单个表值构造函数中插入超过 1000 行。This allows for inserting more than 1000 rows from a single table value constructor.

CREATE TABLE dbo.Test ([Value] int);  
  
INSERT INTO dbo.Test ([Value])  
  SELECT drvd.[NewVal]
  FROM   (VALUES (0), (1), (2), (3), ..., (5000)) drvd([NewVal]);

另请参阅See Also

INSERT (Transact-SQL) INSERT (Transact-SQL)
MERGE (Transact-SQL) MERGE (Transact-SQL)
FROM (Transact-SQL)FROM (Transact-SQL)