Constructeur de valeurs de table (Transact-SQL)

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é dans la clause VALUES de l'instruction INSERT, dans la clause USING <table source> de l'instruction MERGE et dans la définition d'une table dérivée dans la clause FROM.

Conventions de syntaxe de Transact-SQL

Syntaxe

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

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

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

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

Le nombre maximal de lignes qui peuvent être créées à l'aide du constructeur de valeurs de table est 1 000. Pour insérer plus de 1 000 lignes, créez plusieurs instructions INSERT ou importez en bloc les données via l'utilitaire bcp ou l'instruction BULK INSERT.

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 AdventureWorks2008R2;
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 retournée. Par exemple, l'instruction suivante insère un nombre entier et une valeur de 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

Lorsque 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

A. 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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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

L'exemple suivant utilise 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

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 AdventureWorks2008R2;
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;

Voir aussi

Référence

INSERT (Transact-SQL)

MERGE (Transact-SQL)

FROM (Transact-SQL)