在 SQL Server 中，表值構(gòu)造函數(shù)（Table Value Constructor, TVC）是一種用于在單個(gè)語句中插入多行數(shù)據(jù)到表中的語法。它允許你以行內(nèi)表值表達(dá)式（row-valued expression）的形式指定多行數(shù)據(jù)，并將這些數(shù)據(jù)作為一個(gè)整體插入到表中。

1、本文內(nèi)容

• 語法
• 自變量
• 限制和局限
• 數(shù)據(jù)類型
• 示例
• 另請(qǐng)參閱

適用于：

• SQL Server
• Azure SQL 數(shù)據(jù)庫
• Azure SQL 托管實(shí)例

指定要構(gòu)建到某一表中的一組行值表達(dá)式。 Transact-SQL 表值構(gòu)造函數(shù)允許在單個(gè) DML 語句中指定多行數(shù)據(jù)。 表值構(gòu)造函數(shù)可以指定為 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 }

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

若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 語法，請(qǐng)參考如下官方地址

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

3、自變量

• VALUES
  介紹行值表達(dá)式列表。 每個(gè)列表都必須用括號(hào)括起來并由逗號(hào)分隔。

  在每個(gè)列表中指定的值的數(shù)目必須相同，并且值必須采用與表中的列相同的順序。 表中每個(gè)列的值必須指定，或者列列表必須顯式為每個(gè)傳入值指定列。

• DEFAULT
  強(qiáng)制數(shù)據(jù)庫引擎插入為列定義的默認(rèn)值。 如果某列并不存在默認(rèn)值，并且該列允許 Null 值，則插入 NULL。 DEFAULT 對(duì)標(biāo)識(shí)列無效。 當(dāng)在表值構(gòu)造函數(shù)中指定時(shí)，只在 INSERT 語句中允許 DEFAULT。

• expression
  一個(gè)常量、變量或表達(dá)式。 表達(dá)式不能包含 EXECUTE 語句。

4、限制和局限

當(dāng)指定為派生表時(shí)，行數(shù)沒有限制。

當(dāng)用作 INSERT VALUES 語句的 VALUES 子句時(shí)…限制為最多 1000行。 如果行數(shù)超過最大值，則返回錯(cuò)誤 10738。 若要插入超過 1000 行的數(shù)據(jù)，請(qǐng)使用下列方法之一：

• 創(chuàng)建多個(gè) INSERT 語句

• 使用派生表

• 通過使用 bcp 實(shí)用工具、.NET SqlBulkCopy 類、OPENROWSET (BULK …) 或 BULK INSERT語句批量導(dǎo)入數(shù)據(jù)

只允許單個(gè)標(biāo)量值作為行值表達(dá)式。 涉及多列的子查詢不允許作為行值表達(dá)式。 例如，以下代碼導(dǎo)致語法錯(cuò)誤，因?yàn)榈谌齻€(gè)行值表達(dá)式列表包含具有多列的子查詢。

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'鼠標(biāo)', 25.50),  
       (N'鍵盤', 130.00) ,
       (SELECT Title, Price FROM T_Product WHERE ID = 2);  
GO

但是，可以通過單獨(dú)在子查詢中指定每一列，重新編寫該語句。 下面的示例成功地將三行插入 t_MyProducts 表中。

INSERT INTO dbo.t_MyProducts (Name, ListPrice)  
VALUES (N'鼠標(biāo)', 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、數(shù)據(jù)類型

在多行 INSERT 語句中指定的值遵循 UNION ALL 語法的數(shù)據(jù)類型約定屬性。 這會(huì)導(dǎo)致不匹配類型隱式轉(zhuǎn)換到更高優(yōu)先級(jí)的類型。 如果此轉(zhuǎn)換不是所支持的隱式轉(zhuǎn)換，則返回錯(cuò)誤。 例如，以下語句將整數(shù)值和字符值插入到類型為 char 的列中。

CREATE TABLE dbo.t_datatype (col_a INT, col_b CHAR);  
GO  
INSERT INTO dbo.t_datatype  VALUES (1,'a'), (2, 1);  
GO

運(yùn)行 INSERT 語句時(shí)，SQL Server 嘗試將 ‘a’ 轉(zhuǎn)換為整數(shù)，因?yàn)閿?shù)據(jù)類型優(yōu)先級(jí)指示整數(shù)類型的優(yōu)先級(jí)高于字符。 轉(zhuǎn)換失敗，并且返回錯(cuò)誤。 您可以根據(jù)需要顯式轉(zhuǎn)換值，從而避免發(fā)生此錯(cuò)誤。 例如，前面的語句可以編寫為：

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

6、示例

6.1、插入多行數(shù)據(jù)

下面的示例創(chuàng)建表 dbo.t_MyProducts，然后使用表值構(gòu)造函數(shù)將3行數(shù)據(jù)插入到該表中。 由于提供了所有列的值并按表中各列的順序列出這些值，因此不必在列列表中指定列名。

INSERT INTO t_MyProducts
VALUES (N'華為手機(jī)',6888.88), (N'榮耀手機(jī)',6688.99),
       (N'小米手機(jī)',5999.88);  
GO

SELECT * FROM dbo.t_MyProducts

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

下面的示例說明如何在使用表值構(gòu)造函數(shù)向表中插入行時(shí)指定 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 子句中將多個(gè)值指定為派生表

下面的示例在 SELECT 語句的 FROM 子句中使用表值構(gòu)造函數(shù)指定多個(gè)值。

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 語句中將多個(gè)值指定為派生源表

下面的示例使用 MERGE 以更新或插入行的方式來修改 T_MySalesReason 表。 當(dāng)源表中的 NewName 值與目標(biāo)表 NameInfo列中的值匹配時(shí)，就會(huì)更新此目標(biāo)表中的 ReasonType 列。 當(dāng) NewName 的值不匹配時(shí)，就會(huì)將源行插入到目標(biāo)表中。 此源表是一個(gè)派生表，它使用 Transact-SQL 表值構(gòu)造函數(shù)指定源表的多個(gè)行。

-- 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'藍(lá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;

查詢表返回結(jié)果集如下，滿足預(yù)期的結(jié)果

6.5、 插入超過 1000 行

以下示例演示如何將表值構(gòu)造函數(shù)用作派生表。 此方式可從單個(gè)表值構(gòu)造函數(shù)中插入超過 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、另請(qǐng)參閱

• 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

到此這篇關(guān)于SQLServer 表值構(gòu)造函數(shù)(Transact-SQL)的使用的文章就介紹到這了,更多相關(guān)SQL 表值構(gòu)造函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評(píng)論