SQLServer表值构造函数 (Transact-SQL)

在 SQL Server 中，表值构造函数（Table-Valued Constructor）是一种创建表值表达式的方法，它允许你直接在查询中指定一组行，这些行随后可以像表一样被查询、连接或用在其他 SQL 语句中。表值构造函数通常用于 INSERT 语句中直接插入多行数据，或者在 SELECT、UPDATE、DELETE 语句中作为子查询或派生表使用。

1、本文内容

• 语法
• 参数
• 限制和局限
• 数据类型
• 示例

适用于：

• SQL Server
• Azure SQL 数据库
• Azure SQL 托管实例

指定要构建到某一表中的一组行值表达式。 Transact-SQL 表值构造函数允许在单个 DML 语句中指定多行数据。 表值构造函数可以指定为 INSERT VALUES 语句的 VALUES 子句…或指定为 MERGE 语句 USING 子句中的或 FROM 子句中的派生表。

2、语法

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

-- 示例
VALUES (value1, value2, ..., valueN), (value1, value2, ..., valueN), ...

这里，(value1, value2, …, valueN) 表示表中的一行，其中 value1, value2, …, valueN 分别对应表中的列值。你可以根据需要添加多组值来构造多行。

3、参数

• VALUES
  介绍行值表达式列表。 每个列表都必须用括号括起来并由逗号分隔。

  在每个列表中指定的值的数目必须相同，并且值必须采用与表中的列相同的顺序。 表中每个列的值必须指定，或者列列表必须显式为每个传入值指定列。

• DEFAULT
  强制数据库引擎插入为列定义的默认值。 如果某列并不存在默认值，并且该列允许 Null 值，则插入 NULL。 DEFAULT 对标识列无效。 当在表值构造函数中指定时，只在 INSERT 语句中允许 DEFAULT。

• expression
  一个常量、变量或表达式。 表达式不能包含 EXECUTE 语句。

4、限制和局限

当指定为派生表时，行数没有限制。

当用作 INSERT VALUES 语句的 VALUES 子句时…限制为最多 1000行。 如果行数超过最大值，则返回错误 10738。 若要插入超过 1000 行的数据，请使用下列方法之一：

• 创建多个 INSERT 语句

• 使用派生表

• 通过使用 bcp 实用工具、.NET SqlBulkCopy 类、OPENROWSET (BULK …) 或 BULK INSERT语句批量导入数据

只允许单个标量值作为行值表达式。 涉及多列的子查询不允许作为行值表达式。 例如，以下代码导致语法错误，因为第三个行值表达式列表包含具有多列的子查询。

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

-- 执行返回的错误信息
消息 156，级别 15，状态 1，第 1432 行
Incorrect syntax near the keyword 'SELECT'.
消息 102，级别 15，状态 1，第 1432 行
Incorrect syntax near ')'.

但是，可以通过单独在子查询中指定每一列，重新编写该语句。 下面的示例成功地将三行插入 T_Products 表中。

INSERT INTO dbo.T_Products (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

5、数据类型

在多行 INSERT 语句中指定的值遵循 UNION ALL 语法的数据类型约定属性。 这会导致不匹配类型隐式转换到更高优先级的类型。 如果此转换不是所支持的隐式转换，则返回错误。 例如，以下语句将整数值和字符值插入到类型为 char 的列中。

CREATE TABLE dbo.t_datatype (col_a INT, col_b CHAR);  
GO  
INSERT INTO dbo.t_datatype VALUES (1,'a'), (2, 1);  
GO
-- 执行返回错误
消息 245，级别 16，状态 1，第 1444 行
Conversion failed when converting the varchar value 'a' to data type int.

运行 INSERT 语句时，SQL Server 尝试将 ‘a’ 转换为整数，因为数据类型优先级指示为，整数类型的优先级高于字符。 转换失败，并且返回错误。 您可以根据需要显式转换值，从而避免发生此错误。 例如，前面的语句可以编写为：

INSERT INTO dbo.t_datatype VALUES (1,'a'), (2, CONVERT(CHAR,1));
-- OR 
INSERT INTO dbo.t_datatype VALUES (3,'c'), (4, '1');

SELECT * FROM dbo.t_datatype

col_a       col_b
----------- -----
1           a
2           1
3           c
4           1

(4 行受影响)

6、示例

6.1、 插入多行数据

下面的示例表 Production.UnitMeasure，然后使用表值构造函数将三行数据插入到该表中。 由于提供了所有列的值并按表中各列的顺序列出这些值，因此不必在列列表中指定列名。

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

6.2、 使用 DEFAULT 和 NULL 值插入多行

下面的示例说明如何在使用表值构造函数向表中插入行时指定 DEFAULT 和 NULL。

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

SalesReasonID Name                                               ReasonType
------------- -------------------------------------------------- --------------------------------------------------
1             Recommendation                                     Other
2             Advertisement                                      Not Applicable
3             NULL                                               Promotion

(3 行受影响)

6.3、 在 FROM 子句中将多个值指定为派生表

下面的示例在 SELECT 语句的 FROM 子句中使用表值构造函数指定多个值。

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);  
GO  

a           b
----------- -----------
1           2
3           4
5           6
7           8
9           10

(5 行受影响)

-- 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;

ProductID   Name                                               Color
----------- -------------------------------------------------- ---------------
316         Blade                                              NULL
323         Crown Race                                         NULL
712         AWC Logo Cap                                       Multi

(3 行受影响)

6.4、在 MERGE 语句中将多个值指定为派生源表

下面的示例使用 MERGE 以更新或插入行的方式来修改 SalesReason 表。 当源表中的 NewName 值与目标表 (Name) 的 SalesReason 列中的值匹配时，就会更新此目标表中的 ReasonType 列。 当 NewName 的值不匹配时，就会将源行插入到目标表中。 此源表是一个派生表，它使用 Transact-SQL 表值构造函数指定源表的多个行。

USE AdventureWorks2022;  
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;

-- 执行返回结果
(3 行受影响)

Change               CountPerChange
-------------------- --------------
INSERT               2
UPDATE               1

(2 行受影响)

6.5、插入超过 1000 行

以下示例演示如何将表值构造函数用作派生表。 此方式可从单个表值构造函数中插入超过 1000 行。

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

7、 另请参阅

使用MERGE
https://blog.csdn.net/zxrhhm/article/details/140060917?spm=1001.2014.3001.5501