Compartir a través de

Constructor con valores de tabla (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de datos de Azure SQL de Microsoft Fabric

Especifica un conjunto de expresiones de valores de fila que se va a construir en una tabla. El constructor de valor de tabla de Transact-SQL permite que se especifiquen varias filas de datos en una sola instrucción DML. El constructor de valores de tabla se puede especificar como la VALUES cláusula de una INSERT ... VALUES instrucción o como una tabla derivada en la USING cláusula de la MERGE instrucción o en la FROM cláusula .

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

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

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

Argumentos

VALORES

Introduce las listas de expresión de los valores de las filas. Cada lista debe aparecer entre paréntesis y separarse mediante una coma.

El número de valores especificados en cada lista debe ser el mismo y los valores deben estar en el mismo orden que las columnas de la tabla. Se debe especificar un valor para cada columna de la tabla o la lista de columnas debe especificar explícitamente las columnas para cada valor entrante.

PREDETERMINADO

Hace que el motor de base de datos inserte el valor predeterminado definido para una columna. Si no existe un valor predeterminado para la columna y la columna permite valores NULL, NULL se inserta. DEFAULT no es válido para una columna de identidad. Cuando se especifica en un constructor de valores de tabla, DEFAULT solo se permite en una INSERT instrucción .

expresión

Constante, variable o expresión. La expresión no puede contener una EXECUTE instrucción .

Limitaciones

Cuando se usa como una tabla derivada, no hay ningún límite en el número de filas.

Cuando se usa como cláusula VALUES de una INSERT ... VALUES instrucción, hay un límite de 1000 filas. Si el número de filas supera el máximo, se devuelve el error 10738. Para insertar más de 1000 filas, use uno de los métodos siguientes:

Como expresión de valores de fila solo se permiten valores escalares. Como expresión de valores de fila no se permiten las subconsultas que impliquen a varias columnas. Por ejemplo, el código siguiente produce un error de sintaxis porque la tercera lista de expresiones de valores de fila contiene una subconsulta con varias columnas.

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

Sin embargo, la instrucción se puede reescribir especificando cada columna en la subconsulta independientemente. El ejemplo siguiente inserta correctamente tres filas en la tabla MyProducts.

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 datos

Los valores especificados en una instrucción de varias filas INSERT siguen las propiedades de conversión de tipos de datos de la UNION ALL sintaxis. Esto da como resultado la conversión implícita de tipos no coincidentes al tipo de prioridad de tipo de datos superior. Si la conversión no es una conversión implícita admitida, se devuelve un error. Por ejemplo, la instrucción siguiente inserta un valor entero y un valor de carácter en una columna de tipo char.

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

Cuando se ejecuta la INSERT instrucción , SQL Server intenta convertir "a" en un entero porque la precedencia del tipo de datos indica que un entero es de un tipo mayor que un carácter. Se produce un error en la conversión y se devuelve un error. Para evitar este error, puede convertir explícitamente los valores según corresponda. Por ejemplo, la instrucción anterior puede escribirse del siguiente modo.

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

Ejemplos

Un. Insertar varias filas de datos

En el siguiente ejemplo se crea la tabla dbo.Departments y, a continuación, se utiliza el constructor de valor de tabla para insertar cinco filas en la tabla. Dado que los valores para todas las columnas se suministran e incluyen en el mismo orden que las columnas de la tabla, no es necesario especificar los nombres de columna en la lista de columnas.

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. Insertar varias filas con valores DEFAULT y NULL

En el ejemplo siguiente se muestra cómo especificar DEFAULT y NULL cuando se usa el constructor de valores de tabla para insertar filas en una tabla.

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 varios valores como una tabla derivada en una cláusula FROM

En los ejemplos siguientes se usa el constructor de valores de tabla para especificar varios valores en la FROM cláusula de una SELECT instrucción .

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 varios valores como una tabla de origen derivada en una instrucción MERGE

En el ejemplo siguiente se usa MERGE para modificar la SalesReason tabla mediante la actualización o inserción de filas. Cuando el valor de en la tabla de NewName origen coincide con un valor en la Name columna de la tabla de destino (SalesReason), la ReasonType columna se actualiza en la tabla de destino. Cuando el valor de NewName no coincide, la fila del origen se inserta en la tabla de destino. La tabla de origen es una tabla derivada que usa la característica de constructor con valores de tabla de Transact-SQL para especificar varias filas en la tabla de origen.

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. Insertar más de 1000 filas

En el ejemplo siguiente se muestra cómo utilizar el constructor con valores de tabla como una tabla derivada. Esto permite insertar más de 1000 filas desde un único constructor de valores de tabla.

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

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

Contenido relacionado