Table Value Construtor (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Especifica um conjunto de expressões de valor de linha a serem construídas em uma tabela. O construtor Transact-SQL table value permite que várias linhas de dados sejam especificadas em uma única instrução DML. O construtor de valor de tabela pode ser especificado como a VALUES cláusula de uma INSERT ... VALUES instrução ou como uma tabela derivada USING na cláusula da MERGE instrução ou na FROM cláusula.

Transact-SQL convenções de sintaxe

Syntax

VALUES ( <row value expression list> ) [ ,...n ]   

<row value expression list> ::=  
    {<row value expression> } [ ,...n ]  

<row value expression> ::=  
    { DEFAULT | NULL | expression }  

Arguments

VALUES

Apresenta as listas de expressões de valor de linha. Cada lista deve ser colocada entre parênteses e separada por uma vírgula.

O número de valores especificados em cada lista deve ser o mesmo e os valores devem estar na mesma ordem que as colunas na tabela. Um valor para cada coluna na tabela deve ser especificado ou a lista de colunas deve especificar explicitamente as colunas para cada valor de entrada.

DEFAULT

Força o Mecanismo de Banco de Dados a inserir o valor padrão definido para uma coluna. Se não existir um padrão para a coluna e a coluna permitir valores nulos, NULL será inserido. DEFAULT não é válido para uma coluna de identidade. Quando especificado em um construtor de valor de tabela, DEFAULT é permitido somente em uma INSERT instrução.

expression

Uma constante, uma variável ou uma expressão. A expressão não pode conter uma EXECUTE instrução.

Limitations

Quando usado como uma tabela derivada, não há limite para o número de linhas.

Quando usado como a VALUES cláusula de uma INSERT ... VALUES instrução, há um limite de 1.000 linhas. O erro 10738 é retornado se o número de linhas exceder o máximo. Para inserir mais de 1.000 linhas, use um dos seguintes métodos:

• Crie várias INSERT instruções.

• Use uma tabela derivada.

• Importe os dados em massa usando o utilitário bcp, a classe .NET SqlBulkCopy, OPENROWSET BULK ou a instrução BULK INSERT .

Apenas valores escalares únicos são permitidos como uma expressão de valor de linha. Uma subconsulta que envolve várias colunas não é permitida como uma expressão de valor de linha. Por exemplo, o código a seguir resulta em um erro de sintaxe porque a lista de expressões de valor da terceira linha contém uma subconsulta com várias colunas.

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

No entanto, a instrução pode ser reescrita especificando cada coluna na subconsulta separadamente. O exemplo a seguir insere com êxito três linhas na MyProducts tabela.

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  

Tipos de dados

Os valores especificados em uma instrução de várias linhas INSERT seguem as propriedades de conversão de tipo de dados da UNION ALL sintaxe. Isso resulta na conversão implícita de tipos incompatíveis para o tipo de precedência de tipo de dados mais alto. Se a conversão não for uma conversão implícita suportada, um erro será retornado. Por exemplo, a instrução a seguir insere um valor inteiro e um valor de caractere em uma coluna do tipo char.

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

Quando a instrução é executada, o INSERT SQL Server tenta converter 'a' em um inteiro porque a precedência do tipo de dados indica que um inteiro é de um tipo maior do que um caractere. A conversão falha e um erro é retornado. Você pode evitar o erro convertendo explicitamente os valores conforme apropriado. Por exemplo, a declaração anterior pode ser escrita da seguinte forma.

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

Examples

A. Inserir várias linhas de dados

O exemplo a seguir cria a tabela dbo.Departments e, em seguida, usa o construtor de valor de tabela para inserir cinco linhas na tabela. Como os valores para todas as colunas são fornecidos e são listados na mesma ordem que as colunas na tabela, os nomes das colunas não precisam ser especificados na lista de colunas.

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  

B. Inserir várias linhas com valores DEFAULT e NULL

O exemplo a seguir demonstra a especificação DEFAULT e NULL ao usar o construtor de valor de tabela para inserir linhas em uma tabela.

USE AdventureWorks2022;  
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. Especificar vários valores como uma tabela derivada em uma cláusula FROM

Os exemplos a seguir usam o construtor de valor de tabela para especificar vários valores na FROM cláusula de uma SELECT instrução.

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. Especificar vários valores como uma tabela de origem derivada em uma instrução MERGE

O exemplo a seguir usa MERGE para modificar a SalesReason tabela atualizando ou inserindo linhas. Quando o valor de na tabela de NewName origem corresponde a Name um valor na coluna da tabela de destino (SalesReason), a ReasonType coluna é atualizada na tabela de destino. Quando o valor de não corresponde, a linha de origem é inserida na tabela de NewName destino. A tabela de origem é uma tabela derivada que usa o construtor de valor de tabela Transact-SQL para especificar várias linhas para a tabela de origem.

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;  

E. Inserir mais de 1.000 linhas

O exemplo a seguir demonstra o uso do construtor de valor de tabela como uma tabela derivada. Isso permite inserir mais de 1.000 linhas de um único construtor de valor de tabela.

CREATE TABLE dbo.Test ([Value] INT);  

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

Conteúdo relacionado

• INSERIR (Transact-SQL)
• MESCLAR (Transact-SQL)
• Cláusula FROM mais JOIN, APPLY, PIVOT (Transact-SQL)