Tabellenwertkonstruktor (Transact-SQL)

Gibt eine Gruppe mit Zeilenwertausdrücken an, die in einer Tabelle erstellt werden sollen. Der Transact-SQL-Tabellenwertkonstruktor ermöglicht das Angeben mehrerer Datenzeilen in nur einer DML-Anweisung. Der Tabellenwertkonstruktor kann in der VALUES-Klausel der INSERT-Anweisung, in der USING <source table>-Klausel der MERGE-Anweisung und in der Definition einer abgeleiteten Tabelle in der FROM-Klausel angegeben werden.

Transact-SQL-Syntaxkonventionen

Syntax

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

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

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

Argumente

• VALUES
  Bietet eine Einführung in die Zeilenwertausdruckslisten. Die einzelnen Listen müssen in Klammern gesetzt und durch ein Trennzeichen getrennt werden.

  Die Anzahl der in jeder Liste angegebenen Werte muss übereinstimmen, und die Werte müssen in der gleichen Reihenfolge wie die Spalten in der Tabelle vorliegen. Für jede Spalte in der Tabelle muss ein Wert angegeben werden, oder in der Spaltenliste müssen explizit die Spalten für jeden eingehenden Wert angegeben werden.

• DEFAULT
  Erzwingt, dass Database Engine (Datenbankmodul) den für eine Spalte definierten Standardwert einfügt. Wenn für die Spalte kein Standardwert vorhanden ist und die Spalte NULL-Werte zulässt, wird NULL eingefügt. DEFAULT ist für eine Identitätsspalte nicht zulässig. Wenn DEFAULT in einem Tabellenwertkonstruktor angegeben wird, ist diese Variable nur in einer INSERT-Anweisung zulässig.

• expression
  Eine Konstante, eine Variable oder ein Ausdruck. Der Ausdruck darf keine EXECUTE-Anweisung enthalten.

Einschränkungen

Die maximale Anzahl von Zeilen, die mit dem Tabellenwertkonstruktor erstellt werden können, beträgt 1.000. Um mehr als 1.000 Zeilen einzufügen, erstellen Sie mehrere INSERT-Anweisungen, oder führen Sie mit dem Dienstprogramm bcp oder der BULK INSERT-Anweisung einen Massenimport der Daten aus.

Nur einzelne Skalarwerte sind als Zeilenwertausdruck zulässig. Eine Unterabfrage, die mehrere Spalten umfasst, ist nicht als Zeilenwertausdruck zulässig. Der folgende Code verursacht z. B. einen Syntaxfehler, weil die dritte Zeilenwertausdrucksliste eine Unterabfrage mit mehreren Spalten enthält.

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

Die Anweisung kann jedoch umgeschrieben werden, indem jede Spalte getrennt in der Unterabfrage angegeben wird. Im folgenden Beispiel werden drei Zeilen erfolgreich in die MyProducts-Tabelle eingefügt.

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

Datentypen

Die in einer aus mehreren Zeilen bestehenden INSERT-Anweisung angegebenen Werte befolgen die Datentypkonvertierungseigenschaften der UNION ALL-Syntax. Dies führt zur impliziten Konvertierung nicht übereinstimmender Typen in den Typ, der in der Rangfolge höher steht. Wenn es sich bei der Konvertierung nicht um eine unterstützte implizite Konvertierung handelt, gibt das System einen Fehler zurück. In der folgenden Anweisung werden beispielsweise ein ganzzahliger Wert und ein Zeichenwert in eine Spalte vom Typ char eingefügt.

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

Bei Ausführung der INSERT-Anweisung versucht SQL Server, "a" in eine ganze Zahl zu konvertieren, da die Rangfolge der Datentypen angibt, dass eine ganze Zahl einen höherrangigen Typ hat als ein Zeichen. Die Konvertierung schlägt fehl, und es wird ein Fehler zurückgegeben. Sie können den Fehler vermeiden, indem Sie Werte nach Bedarf explizit konvertieren. Die vorherige Anweisung kann beispielsweise folgendermaßen geschrieben werden.

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

Beispiele

A. Einfügen mehrerer Datenzeilen

Im folgenden Beispiel wird die dbo.Departments-Tabelle erstellt; anschließend werden unter Verwendung des Tabellenwertkonstruktors fünf Zeilen in die Tabelle eingefügt. Da Werte für alle Spalten bereitgestellt werden und in der Reihenfolge der Spalten in der Tabelle aufgelistet sind, müssen die Spaltennamen nicht in der Spaltenliste angegeben werden.

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. Einfügen mehrerer Zeilen mit DEFAULT- und NULL-Werten

Im folgenden Beispiel wird das Angeben von DEFAULT und NULL gezeigt, wenn mithilfe des Tabellenwertkonstruktors Zeilen in eine Tabelle eingefügt werden.

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. Angeben mehrerer Werte als abgeleitete Tabelle in einer FROM-Klausel

Im folgenden Beispiel dient der Tabellenwertkonstruktor zur Angabe mehrerer Werte in der FROM-Klausel einer SELECT-Anweisung.

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);
GO

D. Angeben mehrerer Werte als abgeleitete Quelltabelle in einer MERGE-Anweisung

Im folgenden Beispiel wird die SalesReason-Tabelle durch das Aktualisieren oder Einfügen von Zeilen mithilfe von MERGE geändert. Wenn der Wert von NewName in der Quelltabelle einem Wert in der Name-Spalte der Zieltabelle entspricht (SalesReason), wird die ReasonType-Spalte in der Zieltabelle aktualisiert. Wenn der Wert von NewName jedoch nicht übereinstimmt, wird die Quellzeile in die Zieltabelle eingefügt. Die Quelltabelle ist eine abgeleitete Tabelle, die mithilfe des Transact-SQL-Tabellenwertkonstruktors mehrere Zeilen für die Quelltabelle angibt.

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;

Siehe auch

Verweis

INSERT (Transact-SQL)

MERGE (Transact-SQL)

FROM (Transact-SQL)