Constructeur de valeurs de table (Transact-SQL)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Spécifie un ensemble d'expressions de valeurs de ligne à créer dans une table. Le constructeur de valeurs de table Transact-SQL permet de spécifier plusieurs lignes de données dans une seule instruction DML. Le constructeur de valeurs de table peut être spécifié en tant que clause VALUES d’une instruction INSERT ... VALUES ou que table dérivée dans la clause USING de l’instruction MERGE ou dans la clause FROM.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

Remarque

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.

Arguments

VALUES
Introduit les listes d'expressions de valeurs de ligne. Chaque liste doit être placée entre parenthèses et séparée par une virgule.

Le nombre de valeurs spécifiées doit être identique dans chaque liste et les valeurs doivent être dans le même ordre que les colonnes de la table. Une valeur doit être spécifiée pour chaque colonne de la table ou la liste de colonnes doit spécifier explicitement les colonnes pour chaque valeur entrante.

DEFAULT
Force le Moteur de base de données à insérer la valeur par défaut définie pour une colonne. S'il n'existe pas de valeur par défaut pour la colonne et si celle-ci autorise les valeurs NULL, NULL est inséré. DEFAULT n'est pas valide pour une colonne d'identité. Lorsqu'il est spécifié dans un constructeur de valeurs de table, DEFAULT est autorisé uniquement dans une instruction INSERT.

expression
Constante, variable ou expression. L'expression ne peut pas contenir d'instruction EXECUTE.

Limitations et restrictions

Dans le cadre d’une table dérivée, aucune limite ne s’applique au nombre de lignes.

Dans le cadre de la clause VALUES d’une instruction INSERT ... VALUES, il existe une limite de 1 000 lignes. L’erreur 10738 est retournée si le nombre de lignes dépasse la valeur maximale. Pour insérer plus de 1 000 lignes, utilisez une des méthodes suivantes :

Seules les valeurs scalaires uniques sont autorisées en tant qu'expression de valeurs de ligne. Une sous-requête qui implique plusieurs colonnes n'est pas autorisée en tant qu'expression de valeurs de ligne. Par exemple, le code suivant génère une erreur de syntaxe car la troisième liste d'expressions de valeurs de ligne contient une sous-requête avec plusieurs colonnes.

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

Toutefois, l'instruction peut être réécrite en spécifiant séparément chaque colonne dans la sous-requête. L'exemple suivant insère correctement trois lignes dans la table 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

Types de données

Les valeurs spécifiées dans une instruction INSERT portant sur plusieurs lignes respectent les propriétés de conversion de type de données de la syntaxe UNION ALL. Par conséquent, les types incompatibles sont convertis implicitement vers le type ayant la précédence la plus élevée. Si la conversion n'est pas prise en charge en tant que conversion implicite, une erreur est renvoyée. Par exemple, l’instruction suivante insère un nombre entier et un caractère dans une colonne de type char.

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

Quand l’instruction INSERT est exécutée, SQL Server tente de convertir « a » en un nombre entier, car la précédence des types de données indique qu’un nombre entier est prioritaire par rapport à un caractère. La conversion échoue et une erreur est retournée. Vous pouvez éviter cette erreur en utilisant la conversion explicite des valeurs. Par exemple, l'instruction précédente peut être écrite de la façon suivante :

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

Exemples

R. Insertion de plusieurs lignes de données

L'exemple suivant crée la table dbo.Departments, puis utilise le constructeur de valeurs de table pour insérer cinq lignes dans la table. Étant donné que les valeurs de toutes les colonnes sont fournies et qu'elles sont répertoriées dans le même ordre que les colonnes de la table, il n'est pas nécessaire de spécifier les noms de colonnes dans la liste de colonnes.

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. Insertion de plusieurs lignes avec les valeurs DEFAULT et NULL

L'exemple suivant illustre la spécification de DEFAULT et de NULL lors de l'utilisation du constructeur de valeurs de table pour insérer des lignes dans une table.

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. Spécification de plusieurs valeurs sous forme de table dérivée dans une clause FROM

Les exemples suivants utilisent le constructeur de valeurs de table pour spécifier plusieurs valeurs dans la clause FROM d’une instruction SELECT.

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. Spécification de plusieurs valeurs sous forme de table source dérivée dans une instruction MERGE

L'exemple suivant utilise l'instruction MERGE pour modifier la table SalesReason en mettant à jour ou en insérant des lignes. Lorsque la valeur de NewName dans la table source correspond à une valeur de la colonne Name dans la table cible, (SalesReason), la colonne ReasonType est mise à jour dans la table cible. Lorsque la valeur de NewName ne correspond à aucune autre valeur, la ligne source est insérée dans la table cible. La table source est une table dérivée qui utilise le constructeur de valeurs de table Transact-SQL afin de spécifier plusieurs lignes pour la table source.

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. Insertion de plus de 1 000 lignes

L’exemple suivant illustre l’utilisation du constructeur de valeurs de table en tant que table dérivée. Il est ainsi possible d’insérer plus de 1 000 lignes à partir d’un seul constructeur de valeurs de table.

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

Voir aussi

INSERT (Transact-SQL)
MERGE (Transact-SQL)
FROM (Transact-SQL)