Constructeur de valeurs de table (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 valeur de ligne. Une sous-requête qui implique plusieurs colonnes n'est pas autorisée en tant qu'expression de valeur 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 AdventureWorks;
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

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

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

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

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