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

zxrhhm

于 2024-06-29 11:03:52 发布

阅读量1.2k

点赞数 42

文章标签： sqlserver 数据库

本文链接：https://blog.csdn.net/zxrhhm/article/details/140059137

版权

在 SQL Server 中，表值构造函数（Table Value Constructor, TVC）是一种用于在单个语句中插入多行数据到表中的语法。它允许你以行内表值表达式（row-valued expression）的形式指定多行数据，并将这些数据作为一个整体插入到表中。

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 }

Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法，请参考如下官方地址

https://learn.microsoft.com/en-us/sql/sql-server/previous-versions-sql-server?view=sql-server-ver16#offline-documentation

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语句批量导入数据

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

T_Product 表信息如下
在这里插入图片描述

CREATE TABLE dbo.t_MyProducts (NameInfo NVARCHAR(50), Price decimal(18,2));  
GO  
-- This statement fails because the third values list contains multiple columns in the subquery.  
INSERT INTO dbo.t_MyProducts (NameInfo, Price)  
VALUES (N'鼠标', 25.50),  
       (N'键盘', 130.00) ,
       (SELECT Title, Price FROM T_Product WHERE ID = 2);  
GO

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

INSERT INTO dbo.t_MyProducts (Name, ListPrice)  
VALUES (N'鼠标', 25.50),  
       (N'键盘', 130.00) ,
       ((SELECT Title FROM T_Product WHERE ID = 2),  
        (SELECT Price FROM T_Product WHERE ID = 2));  
GO

select * from dbo.t_MyProducts
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

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

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

在这里插入图片描述

6、示例

6.1、插入多行数据

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

INSERT INTO t_MyProducts
VALUES (N'华为手机',6888.88), (N'荣耀手机',6688.99),
       (N'小米手机',5999.88);  
GO

SELECT * FROM dbo.t_MyProducts

在这里插入图片描述

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

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

CREATE TABLE T_MySalesReason
(  
 ID int IDENTITY(1,1) NOT NULL,  
 NameInfo NVARCHAR(32) NULL ,  
 ReasonType NVARCHAR(32) NOT NULL DEFAULT N'新能源汽车' 
);  
GO  
INSERT INTO T_MySalesReason   
VALUES (N'问界M9',N'增程'), (N'比亚迪唐', DEFAULT), (NULL, N'燃油车');  
  
SELECT * FROM T_MySalesReason;

在这里插入图片描述

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

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

SELECT col_a,col_b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(col_a, col_b);  
GO  
-- Used in an inner join to specify values to return.  
SELECT NameInfo,a.ReasonType,b.Name
FROM T_MySalesReason AS a  
  INNER JOIN (VALUES (N'问界M9'), (N'比亚迪唐'), (N'宝马X3')) AS b (Name)   ON a.NameInfo = b.Name;
-- Used in an cross join to specify values to return.  
SELECT NameInfo,a.ReasonType,b.Name
FROM T_MySalesReason AS a  
  CROSS JOIN (VALUES (N'问界M9'), (N'比亚迪唐'), (N'宝马X3')) AS b (Name);

在这里插入图片描述

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

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

-- Create a temporary table variable to hold the output actions.  
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));  
  
MERGE INTO T_MySalesReason AS Target  
USING (VALUES (N'问界M9',N'绿牌'), (N'比亚迪唐', N'绿牌'), (N'宝马X3', N'蓝牌'))  
       AS Source (NewName, NewReasonType)  
ON Target.NameInfo = Source.NewName  
WHEN MATCHED THEN  
  UPDATE SET ReasonType = Source.NewReasonType  
WHEN NOT MATCHED BY TARGET THEN  
  INSERT (NameInfo, 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;

在这里插入图片描述
查询表返回结果集如下，满足预期的结果

6.5、插入超过 1000 行

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

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

7、另请参阅

INSERT (Transact-SQL)
https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver16
MERGE (Transact-SQL)
https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16
FROM (Transact-SQL)
https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver16

欢迎点赞、收藏、转发。。。