INSERT (Transact-SQL)

  • Artikel

Fügt einer Tabelle oder Sicht in SQL Server 2012 eine oder mehrere Zeilen hinzu. Beispiele finden Sie unter Beispiele.

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

[ WITH <common_table_expression> [ ,...n ] ]
INSERT 
{
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited 
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ] 
        | derived_table 
        | execute_statement
        | <dml_table_source>
        | DEFAULT VALUES 
        }
    }
}
[;]

<object> ::=
{ 
    [ server_name . database_name . schema_name . 
      | database_name .[ schema_name ] . 
      | schema_name . 
    ]
  table_or_view_name
}

<dml_table_source> ::=
    SELECT <select_list>
    FROM ( <dml_statement_with_output_clause> ) 
      [AS] table_alias [ ( column_alias [ ,...n ] ) ]
    [ WHERE <search_condition> ]
        [ OPTION ( <query_hint> [ ,...n ] ) ]

<column_definition> ::=
 column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

<data type> ::= 
[ type_schema_name . ] type_name 
    [ ( precision [ , scale ] | max ]

-- External tool only syntax
INSERT 
{
    [BULK]
    [ database_name . [ schema_name ] . | schema_name . ]
    [ table_name | view_name ]
    ( <column_definition> )
    [ WITH (
        [ [ , ] CHECK_CONSTRAINTS ]
        [ [ , ] FIRE_TRIGGERS ]
        [ [ , ] KEEP_NULLS ]
        [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
        [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
        [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
        [ [ , ] TABLOCK ]
    ) ]
}
[; ]

Argumente

  • WITH <common_table_expression>
    Gibt das (auch als allgemeiner Tabellenausdruck bezeichnete) temporäre benannte Resultset an, das innerhalb des Bereichs der INSERT-Anweisung definiert ist. Das Resultset wird von einer SELECT-Anweisung abgeleitet. Weitere Informationen finden Sie unter WITH common_table_expression (Transact-SQL).

  • TOP (expression) [ PERCENT ]
    Gibt die Anzahl oder den Prozentsatz willkürlicher Zeilen an, die eingefügt werden. expression kann eine Anzahl oder ein Prozentsatz der Zeilen sein. Weitere Informationen finden Sie unter TOP (Transact-SQL).

  • INTO
    Ein optionales Schlüsselwort, das zwischen INSERT und der Zieltabelle verwendet werden kann.

  • server_name
    Der Name des Verbindungsservers, auf dem sich die Tabelle oder Sicht befinden. Für server_name kann der Name eines Verbindungsservers angegeben werden, oder Sie verwenden die OPENDATASOURCE-Funktion.

    Wenn server_name als Verbindungsserver angegeben wird, sind database_name und schema_name erforderlich. Wenn server_name mit OPENDATASOURCE angegeben wird, gelten database_name und schema_name möglicherweise nicht für alle Datenquellen und unterliegen den Funktionen des OLE DB-Anbieters, der auf das Remoteobjekt zugreift.

  • database_name
    Ist der Name der Datenbank.

  • schema_name
    Ist der Name des Schemas, zu dem die Tabelle oder Sicht gehört.

  • table_or view_name
    Der Name der Tabelle oder Sicht, die die Daten empfangen soll.

    Innerhalb ihres Bereichs kann eine table-Variable als Tabellenquelle in einer INSERT-Anweisung verwendet werden.

    Die Sicht, auf die table_or_view_name verweist, muss aktualisierbar sein und auf genau eine Basistabelle in der FROM-Klausel der Sicht verweisen. Beispielsweise muss eine INSERT-Anweisung für eine auf mehreren Tabellen basierende Sicht eine column_list verwenden, die nur auf Spalten einer einzigen Basistabelle verweist. Weitere Informationen zu aktualisierbaren Sichten finden Sie unter CREATE VIEW (Transact-SQL).

  • rowset_function_limited
    Die OPENQUERY-Funktion oder die OPENROWSET-Funktion. Die Verwendung dieser Funktionen unterliegt den Funktionen des OLE DB-Anbieters, der auf das Remoteobjekt zugreift.

  • WITH ( <table_hint_limited> [... n ] )
    Gibt mindestens einen Tabellenhinweis an, der für eine Zieltabelle zulässig ist. Das WITH-Schlüsselwort und die Klammern sind erforderlich.

    READPAST, NOLOCK und READUNCOMMITTED sind nicht zulässig. Weitere Informationen zu Tabellenhinweisen finden Sie unter Tabellenhinweise (Transact-SQL).

    Wichtiger HinweisWichtig

    Die Möglichkeit, die Hinweise HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD oder UPDLOCK für Tabellen anzugeben, bei denen es sich um Ziele von INSERT-Anweisungen handelt, wird in einer zukünftigen Version von SQL Server entfernt. Diese Hinweise beeinträchtigen die Leistung von INSERT-Anweisungen nicht. Vermeiden Sie ihre Verwendung bei neuen Entwicklungsarbeiten, und planen Sie die Änderung von Anwendungen, in denen sie aktuell verwendet werden.

    Das Angeben eines TABLOCK-Hinweises für eine Tabelle, die das Ziel einer INSERT-Anweisung ist, hat dieselbe Wirkung wie das Angeben eines TABLOCKX-Hinweises. Auf die Tabelle wird eine exklusive Sperre angewendet.

  • (column_list)
    Eine Liste mit einer oder mehreren Spalten, in die Daten eingefügt werden sollen. column_list muss in Klammern eingeschlossen und durch ein Komma getrennt werden.

    Ist eine Spalte nicht in column_list enthalten, muss Datenbankmodul in der Lage sein, basierend auf der Spaltendefinition einen Wert bereitzustellen. Andernfalls kann die Zeile nicht geladen werden. Datenbankmodul stellt automatisch einen Wert für die Spalte bereit, wenn für sie eine der folgenden Bedingungen erfüllt ist:

    • Besitzt eine IDENTITY-Eigenschaft. Der nächste Identitätswert wird verwendet.

    • Verfügt über einen Standardwert. Der Standardwert der Spalte wird verwendet.

    • Hat den Datentyp timestamp. Der aktuelle Timestampwert wird verwendet.

    • Lässt NULL-Werte zu. Ein NULL-Wert wird verwendet.

    • Die Spalte ist eine berechnete Spalte. Der berechnete Wert wird verwendet.

    column_list muss beim Einfügen von expliziten Werten in eine Identitätsspalte verwendet werden. Dabei muss die Option SET IDENTITY_INSERT für die Tabelle auf ON festgelegt sein.

  • OUTPUT-Klausel
    Gibt eingefügte Zeilen als Teil des Einfügevorgangs zurück. Die Ergebnisse können an die Verarbeitungsanwendung zurückgegeben bzw. zur weiteren Verarbeitung in eine Tabelle oder eine Tabellenvariable eingefügt werden.

    Die OUTPUT-Klausel wird nicht unterstützt in DML-Anweisungen, die auf lokale partitionierte Sichten, verteilte partitionierte Sichten oder Remotetabellen verweisen, oder INSERT-Anweisungen, die execute_statement enthalten. Die OUTPUT INTO-Klausel wird nicht in INSERT-Anweisungen unterstützt, die eine <dml_table_source>-Klausel enthalten.

  • VALUES
    Steht vor der Liste oder den Listen der Datenwerte, die eingefügt werden sollen. Für jede Spalte in column_list (falls angegeben) bzw. in der Tabelle muss ein Datenwert vorhanden sein. Die Werteliste muss in Klammern stehen.

    Wenn die Reihenfolge der Werte in der Werteliste nicht mit der Reihenfolge der Spalten in der Tabelle übereinstimmt oder wenn nicht für jede Spalte in der Tabelle ein Wert vorhanden ist, muss mithilfe von column_list explizit die Spalte angegeben werden, in der ein eingehender Wert gespeichert werden soll.

    Mit dem Transact-SQL-Zeilenkonstruktor (auch Tabellenwertkonstruktor genannt) können mehrere Zeilen in einer einzelnen INSERT-Anweisung angegeben werden. Der Zeilenkonstruktor besteht aus einer einzelnen VALUES-Klausel mit mehreren Wertelisten, die in Klammern eingeschlossen und durch ein Komma getrennt sind. Weitere Informationen finden Sie unter Tabellenwertkonstruktor (Transact-SQL).

  • DEFAULT
    Erzwingt, dass Datenbankmodul den für eine Spalte definierten Standardwert lädt. Wenn für die Spalte kein Standardwert vorhanden ist und die Spalte NULL-Werte zulässt, wird NULL eingefügt. Für eine Spalte, die mit dem Datentyp timestamp definiert ist, wird der nächste Timestampwert eingefügt. DEFAULT ist für eine Identitätsspalte nicht zulässig.

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

    In Verweisen auf die Unicodezeichen-Datentypen nchar, nvarchar und ntext muss expression der Großbuchstabe "N" vorangestellt werden. Wenn "N" nicht angegeben wird, konvertiert SQL Server die Zeichenfolge in die Codepage, die der Standardsortierung der Datenbank oder Spalte entspricht. Alle Zeichen, die in der betreffenden Codepage nicht gefunden werden, gehen verloren.

  • derived_table
    Eine gültige SELECT-Anweisung, die in die Tabelle zu ladende Datenzeilen zurückgibt. Die SELECT-Anweisung kann keinen allgemeinen Tabellenausdruck (Common Table Expression, CTE) enthalten.

  • execute_statement
    Eine gültige EXECUTE-Anweisung, die Daten mithilfe von SELECT- oder READTEXT-Anweisungen zurückgibt. Weitere Informationen finden Sie unter EXECUTE (Transact-SQL).

    Die RESULT SETS-Optionen der EXECUTE-Anweisung können in einer INSERT…EXEC-Anweisung nicht angegeben werden.

    Wenn execute_statement mit INSERT verwendet wird, muss jedes Resultset mit den Spalten in der Tabelle oder in column_list kompatibel sein.

    execute_statement kann zum Ausführen gespeicherter Prozeduren auf dem gleichen Server oder auf einem Remoteserver verwendet werden. Die Prozedur auf dem Remoteserver wird ausgeführt, und die Resultsets werden an den lokalen Server gesendet. Anschließend werden sie in die Tabelle auf dem lokalen Server geladen. In einer verteilten Transaktion kann execute_statement nicht für einen Loopback-Verbindungsserver ausgeführt werden, wenn für die Verbindung Multiple Active Result Sets (MARS) aktiviert ist.

    If execute_statement gibt Daten mit der READTEXT-Anweisung zurück. Jede READTEXT-Anweisung kann maximal 1 MB (1024 KB) Daten zurückgeben. execute_statement kann auch mit erweiterten Prozeduren verwendet werden. execute_statement fügt die durch den Hauptthread der erweiterten Prozedur zurückgegebenen Daten ein. Die Ausgabe von anderen Threads als dem Hauptthread wird hingegen nicht eingefügt.

    Sie können keinen Tabellenwertparameter als Ziel einer INSERT EXEC-Anweisung angeben; er kann jedoch als Quelle in der INSERT EXEC-Zeichenfolge oder der gespeicherter Prozedur angegeben werden. Weitere Informationen finden Sie unter Verwenden von Tabellenwertparameter (Datenbankmodul).

  • <dml_table_source>
    Gibt an, dass es sich bei den in die Zieltabelle eingefügten Zeilen um die von der OUTPUT-Klausel in der INSERT-, UPDATE-, DELETE- oder MERGE-Anweisung zurückgegebenen Zeilen handelt, die optional durch eine WHERE-Klausel gefiltert werden. Wenn <dml_table_source> angegeben ist, muss das Ziel der äußeren INSERT-Anweisung die folgenden Einschränkungen einhalten:

    • Die Tabelle muss eine Basistabelle sein, keine Sicht.

    • Die Tabelle darf keine Remotetabelle sein.

    • Die Tabelle darf keine Definition von aktivierten Triggern besitzen.

    • Die Tabelle darf an keinen Primär-/Fremdschlüsselbeziehungen teilnehmen.

    • Sie darf nicht an Mergereplikationen oder aktualisierbaren Abonnements für Transaktionsreplikationen teilnehmen.

    Der Kompatibilitätsgrad der Datenbank muss auf 100 oder höher festgelegt sein. Weitere Informationen finden Sie unter OUTPUT-Klausel (Transact-SQL).

  • <select_list>
    Eine durch Trennzeichen getrennte Liste, die angibt, welche von der OUTPUT-Klausel zurückgegebenen Spalten eingefügt werden sollen. Die Spalten in <select_list> müssen mit den Spalten kompatibel sein, in die Werte eingefügt werden. <select_list> kann nicht auf Aggregatfunktionen oder TEXTPTR verweisen.

    HinweisHinweis

    In der SELECT-Liste aufgeführte Variablen verweisen auf ihre ursprünglichen Werte, unabhängig von den Änderungen, die in <dml_statement_with_output_clause> daran vorgenommen wurden.

  • <dml_statement_with_output_clause>
    Eine gültige INSERT-, UPDATE-, DELETE- oder MERGE-Anweisung, die betroffene Zeilen in einer OUTPUT-Klausel zurückgibt. Die Anweisung darf keine WITH-Klausel enthalten und sich nicht auf Remotetabellen oder partitionierte Sichten beziehen. Wenn UPDATE oder DELETE angegeben wird, darf kein cursorbasiertes UPDATE oder DELETE verwendet werden. Auf Quellzeilen darf nicht als geschachtelte DML-Anweisungen verwiesen werden.

  • WHERE <search_condition>
    WHERE-Klausel mit gültiger <search_condition>, die die von <dml_statement_with_output_clause> zurückgegebenen Zeilen filtert. Weitere Informationen finden Sie unter Suchbedingung (Transact-SQL). Bei Verwendung in diesem Kontext darf <search_condition> keine Unterabfragen, benutzerdefinierten Skalarfunktionen für einen Datenzugriff, Aggregatfunktionen, TEXTPTR oder Prädikate der Volltextsuche enthalten.

  • DEFAULT VALUES
    Erzwingt, dass die neue Zeile den für jede Spalte definierten Standardwert enthält.

  • BULK
    Wird von externen Tools verwendet, um einen Binärdaten-Datenstrom hochzuladen. Diese Option ist nicht bestimmt zur Verwendung mit Tools wie SQL Server Management Studio, SQLCMD, OSQL oder Datenzugriffsanwendungs-Programmierschnittstellen wie SQL Server Native Client.

  • FIRE_TRIGGERS
    Gibt an, dass INSERT-Trigger, die für die Zieltabelle definiert sind, während des Binärdatenstrom-Uploads ausgeführt werden. Weitere Informationen finden Sie unter BULK INSERT (Transact-SQL).

  • CHECK_CONSTRAINTS
    Gibt an, dass alle Einschränkungen, die für die Zieltabelle oder -sicht gelten, während des Binärdatenstrom-Uploads überprüft werden müssen. Weitere Informationen finden Sie unter BULK INSERT (Transact-SQL).

  • KEEPNULLS
    Gibt an, dass leere Spalten während des Binärdatenstrom-Uploads einen NULL-Wert beibehalten sollen. Weitere Informationen finden Sie unter Beibehalten von NULL-Werten oder Verwenden von Standardwerten während des Massenimports (SQL Server).

  • KILOBYTES_PER_BATCH = kilobytes_per_batch
    Gibt die ungefähre Anzahl der Kilobytes (KB) an Daten pro Batch als kilobytes_per_batch an. Weitere Informationen finden Sie unter BULK INSERT (Transact-SQL).

  • ROWS_PER_BATCH =rows_per_batch
    Gibt die ungefähre Anzahl von Datenzeilen im Binärdatenstrom an. Weitere Informationen finden Sie unter BULK INSERT (Transact-SQL).

    Hinweis: Es wird ein Syntaxfehler ausgelöst, wenn eine Spaltenliste nicht bereitgestellt wird.

Bewährte Methoden

Verwenden Sie die @@ROWCOUNT-Funktion, um die Anzahl der eingefügten Zeilen an die Clientanwendung zurückzugeben. Weitere Informationen finden Sie unter @@ROWCOUNT (Transact-SQL).

Bewährte Methoden für den Massenimport von Daten

Verwenden von INSERT INTO…SELECT für den Massenimport von Daten mit minimaler Protokollierung

Sie können INSERT INTO <target_table> SELECT <columns> FROM <source_table> verwenden, um eine große Anzahl von Zeilen aus einer Tabelle, z. B. einer Stagingtabelle, effizient in eine andere Tabelle mit minimaler Protokollierung zu übertragen. Die minimale Protokollierung kann die Leistung der Anweisung verbessern und die Wahrscheinlichkeit senken, dass der Vorgang den verfügbaren Transaktionsprotokoll-Speicherplatz während der Transaktion auffüllt.

Bei der minimalen Protokollierung für diese Anweisung müssen die folgenden Voraussetzungen erfüllt sein:

  • Das Wiederherstellungsmodell der Datenbank ist auf einfach oder massenprotokolliert festgelegt.

  • Die Zieltabelle ist ein leerer oder nicht leerer Heap.

  • Die Zieltabelle wird nicht in der Replikation verwendet.

  • Der TABLOCK-Hinweis wird für die Zieltabelle angegeben.

Zeilen, die infolge einer Einfügeaktion in eine MERGE-Anweisung in einen Heap eingefügt werden, können ebenfalls minimal protokolliert werden.

Im Gegensatz zur BULK INSERT-Anweisung, die eine weniger restriktive Massenupdatesperre enthält, weist INSERT INTO…SELECT mit dem TABLOCK-Hinweis eine exklusive Sperre (X) für die Tabelle auf. Das bedeutet, dass Sie keine Zeilen mit parallelen Einfügevorgängen einfügen können.

Verwenden von OPENROWSET und BULK für den Massenimport von Daten

Von der OPENROWSET-Funktion können die folgenden Tabellenhinweise akzeptiert werden, die Massenladeoptimierungen mit der INSERT-Anweisung bereitstellen:

  • Der TABLOCK-Hinweis kann die Anzahl der Protokolldatensätze für den Einfügevorgang minimieren. Das Wiederherstellungsmodell der Datenbank muss auf einfach oder massenprotokolliert festgelegt werden, und die Zieltabelle kann nicht in der Replikation verwendet werden. Weitere Informationen finden Sie unter Voraussetzungen für die minimale Protokollierung beim Massenimport.

  • Durch den IGNORE_CONSTRAINTS-Hinweis kann vorübergehend die FOREIGN KEY- und CHECK-Einschränkungsüberprüfung deaktiviert werden.

  • Durch den IGNORE_TRIGGERS-Hinweis kann vorübergehend die Ausführung des Triggers deaktiviert werden.

  • Der KEEPDEFAULTS-Hinweis ermöglicht das Einfügen eines Standardwerts für eine Tabellenspalte (falls vorhanden) anstelle von NULL, wenn der Datensatz keinen Wert für die Spalte aufweist.

  • Der KEEPIDENTITY-Hinweis ermöglicht die Verwendung der Identitätswerte in der importierten Datendatei für die Identitätsspalte in der Zieltabelle.

Diese Optimierungen sind mit denen vergleichbar, die mit dem BULK INSERT-Befehl verfügbar sind. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

Datentypen

Beachten Sie beim Einfügen von Zeilen das folgende Datentypverhalten:

  • Wird ein Wert in Spalten des char-, varchar- oder varbinary-Datentyps geladen, ist die Auffüllung mit Leerstellen oder das Abschneiden nachfolgender Leerstellen (Leerzeichen bei char und varchar, Nullen bei varbinary) abhängig von der Einstellung für SET ANSI_PADDING, die bei der Tabellenerstellung für die Spalte festgelegt wurde. Weitere Informationen finden Sie unter SET ANSI_PADDING (Transact-SQL).

    Die folgende Tabelle zeigt den Standardvorgang für SET ANSI_PADDING OFF.

    Datentyp

    Standardvorgang

    char

    Füllt den Wert mit Leerzeichen auf, bis die definierte Breite der Spalte erreicht ist.

    varchar

    Löscht nachfolgende Leerzeichen bis zum ersten Zeichen, das kein Leerzeichen ist, oder alle Zeichen bis auf eines, wenn die Zeichenfolge nur aus Leerzeichen besteht.

    varbinary

    Löscht nachfolgende Nullen.

  • Wird eine leere Zeichenfolge (' ') in eine Spalte mit dem varchar-Datentyp oder text-Datentyp geladen, wird standardmäßig eine Zeichenfolge der Länge NULL eingefügt.

  • Durch das Einfügen eines NULL-Werts in eine Spalte des text-Datentyps oder image-Datentyps wird kein gültiger Textzeiger erstellt, und es wird vorab auch keine 8-KB-Textseite zugeordnet.

  • Mit dem uniqueidentifier-Datentyp erstellte Spalten enthalten speziell formatierte 16-Byte-Binärwerte. Anders als bei Identitätsspalten generiert Datenbankmodul für Spalten mit dem uniqueidentifier-Datentyp nicht automatisch Werte. Bei einem Einfügevorgang können Variablen mit einem uniqueidentifier-Datentyp und Zeichenfolgenkonstanten der Form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (36 Zeichen inklusive Bindestriche, wobei x für eine Hexadezimalziffer im Bereich 0-9 bzw. a-f steht) für uniqueidentifier-Spalten verwendet werden. Der Wert 6F9619FF-8B86-D011-B42D-00C04FC964FF ist z. B. ein gültiger Wert für eine uniqueidentifier-Variable oder -Spalte. Mit der NEWID()-Funktion kann ein GUID (Globally Unique Identifier) abgerufen werden.

Einfügen von Werten in Spalten eines benutzerdefinierten Typs

Sie können Werte in Spalten eines benutzerdefinierten Typs einfügen, indem Sie eine der folgenden Methoden verwenden:

  • Bereitstellen eines Werts des benutzerdefinierten Typs

  • Bereitstellen eines Werts eines SQL Server-Systemdatentyps, sofern der benutzerdefinierte Typ implizite oder explizite Konvertierung von diesem Typ unterstützt. Das folgende Beispiel zeigt, wie ein Wert durch explizite Konvertierung von einer Zeichenfolge in eine Spalte des benutzerdefinierten Typs Point eingefügt wird.

    INSERT INTO Cities (Location)
VALUES ( CONVERT(Point, '12.3:46.2') );

    Ein binärer Wert kann auch ohne explizite Konvertierung bereitgestellt werden, da alle benutzerdefinierten Typen implizit aus binären Werten konvertierbar sind.

  • Aufrufen einer benutzerdefinierten Funktion, die einen Wert des benutzerdefinierten Typs zurückgibt. Das folgende Beispiel verwendet die benutzerdefinierte Funktion CreateNewPoint(), um einen neuen Wert des benutzerdefinierten Typs Point zu erstellen und den Wert in die Cities-Tabelle einzufügen.

    INSERT INTO Cities (Location)
VALUES ( dbo.CreateNewPoint(x, y) );

Fehlerbehandlung

Sie können die Fehlerbehandlung für die INSERT-Anweisung durch Angeben der Anweisung in einem TRY…CATCH-Konstrukt implementieren.

Wenn eine INSERT-Anweisung eine Einschränkung oder Regel verletzt bzw. die Anweisung einen Wert enthält, der mit dem Datentyp der Spalte nicht kompatibel ist, schlägt die Anweisung fehl, und eine Fehlermeldung wird zurückgegeben.

Wenn INSERT mehrere Zeilen mit SELECT oder EXECUTE lädt, bewirkt eine Verletzung einer Regel oder Einschränkung beim Laden der Werte, dass die Anweisung beendet und keine Zeile geladen wird.

Wenn in einer INSERT-Anweisung bei der Auswertung eines Ausdrucks ein arithmetischer Fehler (Überlauf, Division durch Null oder Domänenfehler) auftritt, behandelt Datenbankmodul den Fehler so, als wäre SET ARITHABORT auf ON festgelegt. Der Batch wird beendet, und eine Fehlermeldung wird zurückgegeben. Wenn in einer Anweisung INSERT, DELETE oder UPDATE ein arithmetischer Fehler (Überlauf, Division durch 0 (null) oder Bereichsfehler) bei der Auswertung eines Ausdrucks auftritt und SET ARITHABORT und SET ANSI_WARNINGS auf OFF festgelegt ist, fügt SQL Server einen NULL-Wert ein oder aktualisiert ihn. Wenn die Zielspalte keine NULL-Werte zulässt, schlägt das Einfügen oder Aktualisieren fehl, und dem Benutzer wird ein Fehler angezeigt.

Interoperabilität

Wenn ein INSTEAD OF-Trigger für INSERT-Aktionen für eine Tabelle oder Sicht definiert ist, wird der Trigger anstelle der INSERT-Anweisung ausgeführt. Weitere Informationen zu INSTEAD OF-Triggern finden Sie unter CREATE TRIGGER (Transact-SQL).

Einschränkungen

Wenn in Remotetabellen Werte eingefügt und nicht alle Werte für alle Spalten angegeben werden, müssen Sie die Spalten identifizieren, in denen die angegebenen Werte eingefügt werden sollen.

Wenn TOP mit INSERT verwendet wird, werden die Zeilen, auf die verwiesen wird, nicht auf bestimmte Weise angeordnet, und die ORDER BY-Klausel kann in dieser Anweisung nicht direkt angegeben werden. Wenn Sie die TOP-Klausel verwenden müssen, um Zeilen in einer sinnvollen Reihenfolge einzufügen, müssen Sie sie zusammen mit einer ORDER BY-Klausel in einer untergeordneten SELECT-Anweisung verwenden. Weitere Informationen finden Sie im Abschnitt "Beispiele" in diesem Thema.

Protokollierungsverhalten

Die INSERT-Anweisung wird immer vollständig protokolliert, sofern nicht die OPENROWSET-Funktion mit dem BULK-Schlüsselwort oder INSERT INTO <target_table> SELECT <columns> FROM <source_table> verwendet wird. Für diese Vorgänge ist eine minimale Protokollierung möglich. Weitere Informationen finden Sie im Abschnitt "Bewährte Methoden zum Massenladen von Daten" weiter oben in diesem Thema.

Sicherheit

Im Verlauf der Verbindung mit einem Verbindungsserver stellt der sendende Server einen Benutzernamen und ein Kennwort bereit, um eine Verbindung mit dem empfangenden Server in dessen Auftrag aufzubauen. Damit diese Verbindung funktioniert, müssen Sie mithilfe von sp_addlinkedsrvlogin eine Anmeldenamenzuordnung zwischen den Verbindungsservern erstellen.

Für die Verwendung von OPENROWSET(BULK…) ist es wichtig, nachvollziehen zu können, wie in SQL Server mit Identitätswechseln umgegangen wird. Weitere Informationen finden Sie im Abschnitt "Überlegungen zur Sicherheit" unter Importieren von Massendaten mithilfe von BULK INSERT oder OPENROWSET(BULK...) (SQL Server).

Berechtigungen

Die INSERT-Berechtigung ist für die Zieltabelle erforderlich.

Standardmäßig verfügen Mitglieder der festen Serverrolle sysadmin, der festen Datenbankrollen db_owner und db_datawriter sowie Tabellenbesitzer über INSERT-Berechtigungen. Mitglieder der Rollen sysadmin, db_owner und db_securityadmin sowie der Tabellenbesitzer können Berechtigungen an andere Benutzer übertragen.

Zum Ausführen von INSERT mit der BULK-Option der OPENROWSET-Funktion müssen Sie Mitglied der festen Serverrolle sysadmin oder der festen Serverrolle bulkadmin sein.

Beispiele

Kategorie

Funktionssyntaxelemente

Grundlegende Syntax

INSERT • Tabellenwertkonstruktor

Behandlung von Spaltenwerten

IDENTITY • NEWID • Standardwerte • Benutzerdefinierte Typen

Einfügen von Daten aus anderen Tabellen

INSERT…SELECT • INSERT…EXECUTE • WITH Allgemeiner Tabellenausdruck • TOP • OFFSET FETCH

Angeben von Zielobjekten, die keine Standardtabellen sind

Views • Tabellenvariablen

Einfügen von Zeilen in eine Remotetabelle

Verbindungsserver • OPENQUERY-Rowsetfunktion • OPENDATASOURCE-Rowsetfunktion

Massenladen von Daten aus Tabellen oder Datendateien

INSERT…SELECT • OPENROWSET-Funktion

Überschreiben des Standardverhaltens des Abfrageoptimierers mithilfe von Hinweisen

Tabellenhinweise

Erfassen der Ergebnisse der INSERT-Anweisung

OUTPUT-Klausel

Grundlegende Syntax

Anhand von Beispielen in diesem Abschnitt wird die grundlegende Funktion der INSERT-Anweisung mithilfe der mindestens erforderlichen Syntax veranschaulicht.

A.Einfügen einer einzelnen Datenzeile

Im folgenden Beispiel wird eine Zeile in die Production.UnitMeasure-Tabelle eingefügt. Die Spalten in dieser Tabelle heißen UnitMeasureCode, Name und ModifiedDate. 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 AdventureWorks2012;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT', N'Feet', '20080414');
GO

B.Einfügen mehrerer Datenzeilen

Im folgenden Beispiel werden mit dem Tabellenwertkonstruktor drei Zeilen in die Production.UnitMeasure-Tabelle in einer einzelnen INSERT-Anweisung eingefügt. Da Werte für alle Spalten bereitgestellt werden und entsprechend der Reihenfolge der Spalten in der Tabelle aufgelistet sind, müssen die Spaltennamen nicht in der Spaltenliste angegeben werden.

USE AdventureWorks2012;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');
GO

C.Einfügen von Daten, deren Reihenfolge nicht mit der Reihenfolge der Tabellenspalten übereinstimmt

Im folgenden Beispiel wird eine Spaltenliste verwendet, um die in jede Spalte einzufügenden Werte explizit anzugeben. Die Spaltenreihenfolge in der Production.UnitMeasure-Tabelle lautet UnitMeasureCode, Name, ModifiedDate. Die Spalten sind jedoch nicht in dieser Reihenfolge in column_list aufgelistet.

USE AdventureWorks2012;
GO
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,
    ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());
GO

Behandlung von Spaltenwerten

In den Beispielen in diesem Abschnitt werden Methoden zum Einfügen von Werten in Spalten erläutert, die mit einer IDENTITY-Eigenschaft und einem DEFAULT-Wert oder mit Datentypen wie uniqueidentifer oder Spalten eines benutzerdefinierten Typs definiert werden.

A.Einfügen von Daten in eine Tabelle mit Spalten, die Standardwerte enthalten

Im folgenden Beispiel wird das Einfügen von Zeilen in eine Tabelle mit Spalten gezeigt, die automatisch einen Wert generieren oder einen Standardwert haben. Column_1 ist eine berechnete Spalte, die automatisch einen Wert generiert, indem eine Zeichenfolge mit dem in column_2 eingefügten Wert verkettet wird. Column_2 wird als Standardeinschränkung definiert. Wenn für diese Spalte kein Wert angegeben ist, wird der Standardwert verwendet. Column_3 ist mit dem Datentyp rowversion definiert, der automatisch eine eindeutige, inkrementelle Binärzahl generiert. Column_4 generiert nicht automatisch einen Wert. Wird für diese Spalte kein Wert definiert, wird NULL eingefügt. Die INSERT-Anweisungen fügen Zeilen ein, die Werte für einige (aber nicht alle) Spalten enthalten. In der letzten INSERT-Anweisung werden keine Spalten angegeben, und nur die Standardwerte werden mithilfe der DEFAULT VALUES-Klausel eingefügt.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 AS 'Computed column ' + column_2, 
    column_2 varchar(30) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 rowversion,
    column_4 varchar(40) NULL
);
GO
INSERT INTO dbo.T1 (column_4) 
    VALUES ('Explicit value');
INSERT INTO dbo.T1 (column_2, column_4) 
    VALUES ('Explicit value', 'Explicit value');
INSERT INTO dbo.T1 (column_2) 
    VALUES ('Explicit value');
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2, column_3, column_4
FROM dbo.T1;
GO

B.Einfügen von Daten in eine Tabelle mit einer Identitätsspalte

Im folgenden Beispiel werden verschiedene Methoden zum Einfügen von Daten in eine Identitätsspalte gezeigt. Die ersten beiden INSERT-Anweisungen ermöglichen das Generieren von Identitätswerten für die neuen Zeilen. Die dritte INSERT-Anweisung überschreibt die IDENTITY-Eigenschaft für die Spalte mit der SET IDENTITY_INSERT-Anweisung und fügt in die Identitätsspalte einen expliziten Wert ein.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30));
GO
INSERT T1 VALUES ('Row #1');
INSERT T1 (column_2) VALUES ('Row #2');
GO
SET IDENTITY_INSERT T1 ON;
GO
INSERT INTO T1 (column_1,column_2) 
    VALUES (-99, 'Explicit identity value');
GO
SELECT column_1, column_2
FROM T1;
GO

C.Einfügen von Daten in eine uniqueidentifier-Spalte mithilfe von NEWID()

Im folgenden Beispiel wird die NEWID()-Funktion verwendet, um eine GUID für column_2 zu erhalten. Im Gegensatz zu Identitätsspalten generiert Datenbankmodul nicht automatisch Werte für Spalten mit dem uniqueidentifier-Datentyp, wie durch die zweite INSERT-Anweisung gezeigt.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 int IDENTITY, 
    column_2 uniqueidentifier,
);
GO
INSERT INTO dbo.T1 (column_2) 
    VALUES (NEWID());
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2
FROM dbo.T1;
GO

D.Einfügen von Daten in Spalten eines benutzerdefinierten Typs

Mit den folgenden Transact-SQL-Anweisungen werden drei Zeilen in die PointValue-Spalte der Points-Tabelle eingefügt. Für diese Spalte wird ein CLR-benutzerdefinierter Typ (UDT) verwendet. Der Point-Datentyp besteht aus X- und Y-Ganzzahlwerten, die als Eigenschaften des benutzerdefinierten Typs verfügbar gemacht werden. Verwenden Sie entweder die CAST- oder CONVERT-Funktion, um die durch Kommas getrennten X- und Y-Werte in den Point-Typ umzuwandeln. Die ersten beiden Anweisungen verwenden die CONVERT-Funktion, um einen Zeichenfolgenwert in den Point-Typ zu konvertieren. Die dritte Anweisung verwendet die CAST-Funktion: Weitere Informationen finden Sie unter Bearbeiten von UDT-Daten.

INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));

Einfügen von Daten aus anderen Tabellen

Anhand von Beispielen in diesem Abschnitt werden Methoden zum Einfügen von Zeilen aus einer Tabelle in eine andere Tabelle gezeigt.

A.Verwenden der SELECT- und EXECUTE-Option zum Einfügen von Daten aus anderen Tabellen

Im folgenden Beispiel wird gezeigt, wie Daten mithilfe von INSERT…SELECT oder INSERT…EXECUTE aus einer Tabelle in eine andere Tabelle eingefügt werden. Jede Methode basiert auf einer SELECT-Anweisung mit mehreren Tabellen, die einen Ausdruck und einen Literalwert in der Spaltenliste enthält.

Bei der ersten INSERT-Anweisung wird direkt eine SELECT-Anweisung verwendet, um Daten aus den Quelltabellen (Employee, SalesPerson und Person) abzuleiten und das Resultset in der EmployeeSales -Tabelle zu speichern. Für die zweite INSERT-Anweisung wird die EXECUTE-Klausel verwendet, um eine gespeicherte Prozedur aufzurufen, die die SELECT-Anweisung enthält, wohingegen für die dritte INSERT-Anweisung mithilfe der EXECUTE-Klausel auf die SELECT-Anweisung als Literalzeichenfolge verwiesen wird.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
IF OBJECT_ID ('dbo.uspGetEmployeeSales', 'P') IS NOT NULL
    DROP PROCEDURE uspGetEmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( DataSource   varchar(20) NOT NULL,
  BusinessEntityID   varchar(11) NOT NULL,
  LastName     varchar(40) NOT NULL,
  SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales 
AS 
    SET NOCOUNT ON;
    SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName, 
        sp.SalesYTD 
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE '2%'
    ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...SELECT example
INSERT INTO dbo.EmployeeSales
    SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD 
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE '2%'
    ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT INTO dbo.EmployeeSales 
EXECUTE dbo.uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT INTO dbo.EmployeeSales 
EXECUTE 
('
SELECT ''EXEC STRING'', sp.BusinessEntityID, c.LastName, 
    sp.SalesYTD 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE ''2%''
    ORDER BY sp.BusinessEntityID, c.LastName
');
GO
--Show results.
SELECT DataSource,BusinessEntityID,LastName,SalesDollars
FROM dbo.EmployeeSales;
GO

B.Definieren der eingefügten Daten mithilfe von WITH (allgemeiner Tabellenausdruck)

Im folgenden Beispiel wird die NewEmployee-Tabelle erstellt. Durch einen allgemeinen Tabellenausdruck (EmployeeTemp) werden die aus mindestens einer Tabelle in die NewEmployee-Tabelle einzufügenden Zeilen definiert. Die INSERT-Anweisung verweist auf die Spalten im allgemeinen Tabellenausdruck.

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
    DROP TABLE HumanResources.NewEmployee;
GO
CREATE TABLE HumanResources.NewEmployee
(
    EmployeeID int NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    PhoneNumber Phone NULL,
    AddressLine1 nvarchar(60) NOT NULL,
    City nvarchar(30) NOT NULL,
    State nchar(3) NOT NULL, 
    PostalCode nvarchar(15) NOT NULL,
    CurrentFlag Flag
);
GO
WITH EmployeeTemp (EmpID, LastName, FirstName, Phone, 
                   Address, City, StateProvince, 
                   PostalCode, CurrentFlag)
AS (SELECT 
       e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
       a.AddressLine1, a.City, sp.StateProvinceCode, 
       a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN Person.BusinessEntityAddress AS bea
        ON e.BusinessEntityID = bea.BusinessEntityID
        INNER JOIN Person.Address AS a
        ON bea.AddressID = a.AddressID
        INNER JOIN Person.PersonPhone AS pp
        ON e.BusinessEntityID = pp.BusinessEntityID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Person as c
        ON e.BusinessEntityID = c.BusinessEntityID
    )
INSERT INTO HumanResources.NewEmployee 
    SELECT EmpID, LastName, FirstName, Phone, 
           Address, City, StateProvince, PostalCode, CurrentFlag
    FROM EmployeeTemp;
GO

C.Beschränken der aus der Quelltabelle eingefügten Daten mit TOP

Im folgenden Beispiel wird die EmployeeSales-Tabelle erstellt, und der Name und die Verkaufszahlen des laufenden Jahres für die ersten 5 zufälligen Mitarbeiter aus der HumanResources.Employee-Tabelle werden eingefügt. Die INSERT-Anweisung wählt fünf beliebige Zeilen aus, die von der SELECT-Anweisung zurückgegeben werden. Mit der OUTPUT-Klausel werden die Zeilen angezeigt, die in die EmployeeSales-Tabelle eingefügt werden. Beachten Sie, dass die ersten 5 Mitarbeiter in der SELECT-Anweisung nicht mit der ORDER BY-Klausel ermittelt werden.

USE AdventureWorks2012 ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   nvarchar(11) NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  YearlySales  money NOT NULL
 );
GO
INSERT TOP(5)INTO dbo.EmployeeSales
    OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales
    SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD 
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.SalesYTD > 250000.00
    ORDER BY sp.SalesYTD DESC;

Wenn Sie die TOP-Klausel verwenden müssen, um Zeilen in einer sinnvollen Reihenfolge einzufügen, müssen Sie sie zusammen mit einer ORDER BY-Klausel in einer untergeordneten SELECT-Anweisung verwenden, wie im folgenden Beispiel veranschaulicht. Mit der OUTPUT-Klausel werden die Zeilen angezeigt, die in die EmployeeSales-Tabelle eingefügt werden. Beachten Sie, dass die ersten 5 Mitarbeiter jetzt anhand der Ergebnisse der ORDER BY-Klausel und nicht anhand zufälliger Zeilen eingefügt werden.

INSERT INTO dbo.EmployeeSales
    OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales
    SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD 
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.SalesYTD > 250000.00
    ORDER BY sp.SalesYTD DESC;

Angeben von Zielobjekten, die keine Standardtabellen sind

In den Beispielen in diesem Abschnitt wird veranschaulicht, wie Zeilen durch Angeben einer Sicht oder Tabellenvariablen eingefügt werden.

A.Einfügen von Daten durch Angeben einer Sicht

Im folgenden Beispiel wird ein Sichtname als Zielobjekt angegeben. Die neue Zeile wird jedoch in die zugrunde liegende Basistabelle eingefügt. Die Reihenfolge der Werte in der INSERT-Anweisung muss mit der Reihenfolge der Spalten in der Sicht übereinstimmen. Weitere Informationen finden Sie unter Ändern von Daten über eine Sicht.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
IF OBJECT_ID ('dbo.V1', 'V') IS NOT NULL
    DROP VIEW dbo.V1;
GO
CREATE TABLE T1 ( column_1 int, column_2 varchar(30));
GO
CREATE VIEW V1 AS 
SELECT column_2, column_1 
FROM T1;
GO
INSERT INTO V1 
    VALUES ('Row 1',1);
GO
SELECT column_1, column_2 
FROM T1;
GO
SELECT column_1, column_2
FROM V1;
GO

B.Einfügen von Daten in eine Tabellevariable

Im folgenden Beispiel wird eine Tabellenvariable als Zielobjekt angegeben.

USE AdventureWorks2012;
GO
-- Create the table variable.
DECLARE @MyTableVar table(
    LocationID int NOT NULL,
    CostRate smallmoney NOT NULL,
    NewCostRate AS CostRate * 1.5,
    ModifiedDate datetime);

-- Insert values into the table variable.
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)
    SELECT LocationID, CostRate, GETDATE() FROM Production.Location
    WHERE CostRate > 0;

-- View the table variable result set.
SELECT * FROM @MyTableVar;
GO

Einfügen von Zeilen in eine Remotetabelle

In den Beispielen in diesem Abschnitt wird veranschaulicht, wie Zeilen mit einem Verbindungsserver oder einer Rowsetfunktion in eine Remotezieltabelle eingefügt werden, um auf die Remotetabelle zu verweisen.

A.Einfügen von Daten in eine Remotetabelle mithilfe eines Verbindungsservers

Im folgenden Beispiel werden Zeilen in eine Remotetabelle eingefügt. In diesem Beispiel wird zunächst mithilfe von sp_addlinkedserver ein Link zur Remotedatenquelle erstellt. Der Name des Verbindungsservers (MyLinkServer) wird anschließend als Teil des vierteiligen Objektnamens in der Form server.catalog.schema.object angegeben.

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'server_name',
    @catalog = N'AdventureWorks2012';
GO

USE AdventureWorks2012;
GO
-- Specify the remote data source in the FROM clause using a four-part name 
-- in the form linked_server.catalog.schema.object.

INSERT INTO MyLinkServer.AdventureWorks2012.HumanResources.Department (Name, GroupName)
VALUES (N'Public Relations', N'Executive General and Administration');
GO

B.Einfügen von Daten in eine Remotetabelle mithilfe der OPENQUERY-Funktion

Im folgenden Beispiel wird durch Angabe der OPENQUERY-Rowsetfunktion eine Zeile in eine Remotetabelle eingefügt. Der im vorherigen Beispiel erstellte Name des Verbindungsservers wird hier verwendet.

-- Use the OPENQUERY function to access the remote data source.

INSERT OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2012.HumanResources.Department')
VALUES ('Environmental Impact', 'Engineering');
GO

C.Einfügen von Daten in eine Remotetabelle mithilfe der OPENDATASOURCE-Funktion

Im folgenden Beispiel wird durch Angabe der OPENDATASOURCE-Rowsetfunktion eine Zeile in eine Remotetabelle eingefügt. Geben Sie mit dem Format server_name oder server_name\instance_name einen gültigen Servernamen für die Datenquelle an.

-- Use the OPENDATASOURCE function to specify the remote data source.
-- Specify a valid server name for Data Source using the format server_name or server_name\instance_name.

INSERT INTO OPENDATASOURCE('SQLNCLI',
    'Data Source= <server_name>; Integrated Security=SSPI')
    .AdventureWorks2012.HumanResources.Department (Name, GroupName)
    VALUES (N'Standards and Methods', 'Quality Assurance');
GO

Massenladen von Daten aus Tabellen oder Datendateien

In den Beispielen in diesem Abschnitt werden zwei Methoden zum Massenladen von Daten in eine Tabelle mithilfe der INSERT-Anweisung vorgestellt.

A.Einfügen von Daten mit minimaler Protokollierung in einen Heap

Im folgenden Beispiel wird eine neue Tabelle (ein Heap) erstellt, und es werden Daten aus einer anderen Tabelle in die neu erstellte Tabelle eingefügt. Dazu wird minimale Protokollierung verwendet. Im Beispiel wird davon ausgegangen, dass das Wiederherstellungsmodell der AdventureWorks2012-Datenbank auf FULL festgelegt wird. Um sicherzustellen, dass die minimale Protokollierung verwendet wird, wird das Wiederherstellungsmodell der AdventureWorks2012-Datenbank auf BULK_LOGGED festgelegt, bevor Zeilen eingefügt und nach der INSERT INTO…-SELECT-Anweisung auf FULL zurückgesetzt werden. Außerdem wird der TABLOCK-Hinweis für die Sales.SalesHistory-Zieltabelle angegeben. Dadurch wird sichergestellt, dass die Anweisung minimalen Speicherplatz im Transaktionsprotokoll verwendet und effektiv ausgeführt wird.

USE AdventureWorks2012;
GO
-- Create the target heap.
CREATE TABLE Sales.SalesHistory(
    SalesOrderID int NOT NULL,
    SalesOrderDetailID int NOT NULL,
    CarrierTrackingNumber nvarchar(25) NULL,
    OrderQty smallint NOT NULL,
    ProductID int NOT NULL,
    SpecialOfferID int NOT NULL,
    UnitPrice money NOT NULL,
    UnitPriceDiscount money NOT NULL,
    LineTotal money NOT NULL,
    rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
    ModifiedDate datetime NOT NULL );
GO
-- Temporarily set the recovery model to BULK_LOGGED.
ALTER DATABASE AdventureWorks2012
SET RECOVERY BULK_LOGGED;
GO
-- Transfer data from Sales.SalesOrderDetail to Sales.SalesHistory
INSERT INTO Sales.SalesHistory WITH (TABLOCK)
    (SalesOrderID, 
     SalesOrderDetailID,
     CarrierTrackingNumber, 
     OrderQty, 
     ProductID, 
     SpecialOfferID, 
     UnitPrice, 
     UnitPriceDiscount,
     LineTotal, 
     rowguid, 
     ModifiedDate)
SELECT * FROM Sales.SalesOrderDetail;
GO
-- Reset the recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO

B.Verwenden der OPENROWSET-Funktion mit BULK zum Massenladen von Daten in eine Tabelle

Im folgenden Beispiel werden durch Angabe der OPENROWSET-Funktion Zeilen aus einer Datendatei in eine Tabelle eingefügt. Der IGNORE_TRIGGERS-Tabellenhinweis wird zur Leistungsoptimierung angegeben. Weitere Beispiele finden Sie unter Importieren von Massendaten mithilfe von BULK INSERT oder OPENROWSET(BULK...) (SQL Server).

-- Use the OPENROWSET function to specify the data source and specifies the IGNORE_TRIGGERS table hint.
INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)
SELECT b.Name, b.GroupName 
FROM OPENROWSET (
    BULK 'C:\SQLFiles\DepartmentData.txt',
    FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
    ROWS_PER_BATCH = 15000)AS b ;
GO

Überschreiben des Standardverhaltens des Abfrageoptimierers mithilfe von Hinweisen

In den Beispielen in diesem Abschnitt wird gezeigt, wie mit Tabellenhinweisen beim Verarbeiten der INSERT-Anweisung zeitweise das Standardverhalten des Abfrageoptimierers überschrieben wird.

VorsichtshinweisVorsicht

Da der SQL Server-Abfrageoptimierer in der Regel den optimalen Ausführungsplan für eine Abfrage auswählt, wird empfohlen, dass erfahrene Entwickler und Datenbankadministratoren Hinweise nur dann verwenden, wenn alle anderen Möglichkeiten sich als unzureichend erwiesen haben.

A.Verwenden des TABLOCK-Hinweises zum Angeben einer Sperrmethode

Im folgenden Beispiel wird angegeben, dass eine exklusive Sperre (X) für die Production.Location-Tabelle eingerichtet und bis zum Ende der INSERT-Anweisung aufrechterhalten wird.

USE AdventureWorks2012;
GO
INSERT INTO Production.Location WITH (XLOCK)
(Name, CostRate, Availability)
VALUES ( N'Final Inventory', 15.00, 80.00);
GO

Erfassen der Ergebnisse der INSERT-Anweisung

In den Beispielen in diesem Abschnitt wird gezeigt, wie mit der OUTPUT-Klausel Informationen aus jeder von einer INSERT-Anweisung betroffenen Zeile bzw. Ausdrücke, die auf jeder Zeile basieren, zurückgegeben werden. Diese Ergebnisse können an die verarbeitende Anwendung zurückgegeben werden, die sie z. B. für Bestätigungen, Archivierungen und andere Anwendungsanforderungen verwendet.

A. Verwenden von OUTPUT mit einer INSERT-Anweisung

Im folgenden Beispiel wird eine Zeile in die ScrapReason-Tabelle eingefügt, und die OUTPUT-Klausel wird verwendet, um die Ergebnisse der Anweisung an die @MyTableVar-Tabellenvariable zurückzugeben. Da die ScrapReasonID-Spalte mit einer IDENTITY-Eigenschaft definiert ist, wird kein Wert für diese Spalte in der INSERT-Anweisung angegeben. Beachten Sie jedoch, dass der von Datenbankmodul für diese Spalte generierte Wert in der OUTPUT-Klausel in der INSERTED.ScrapReasonID-Spalte zurückgegeben wird.

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

B.Verwenden von OUTPUT mit Identitätsspalten und berechneten Spalten

Im folgenden Beispiel wird die EmployeeSales-Tabelle erstellt, und es werden mehrere Zeilen mithilfe einer INSERT-Anweisung mit einer SELECT-Anweisung zum Abrufen der Daten aus den Quelltabellen in die Tabelle eingefügt. Die EmployeeSales-Tabelle enthält eine Identitätsspalte (EmployeeID) und eine berechnete Spalte (ProjectedSales). Da diese Werte während des Einfügevorgangs von Datenbankmodul generiert werden, kann keine dieser Spalten in @MyTableVar definiert werden.

USE AdventureWorks2012 ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   int IDENTITY (1,5)NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL,
  ProjectedSales AS CurrentSales * 1.10 
);
GO
DECLARE @MyTableVar table(
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL
  );

INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
  OUTPUT INSERTED.LastName, 
         INSERTED.FirstName, 
         INSERTED.CurrentSales
  INTO @MyTableVar
    SELECT c.LastName, c.FirstName, sp.SalesYTD
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE '2%'
    ORDER BY c.LastName, c.FirstName;

SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO

C.Einfügen von Daten, die von einer OUTPUT-Klausel zurückgegeben wurden

Im folgenden Beispiel werden aus der OUTPUT-Klausel einer MERGE-Anweisung zurückgegebene Daten erfasst und in eine andere Tabelle eingefügt. Die MERGE-Anweisung aktualisiert die Quantity-Spalte der ProductInventory-Tabelle täglich auf der Grundlage der Bestellungen, die in der SalesOrderDetail-Tabelle verarbeitet werden. Außerdem werden die Zeilen für Produkte gelöscht, deren Bestand auf 0 (null) fällt. Das Beispiel erfasst die gelöschten Zeilen und fügt sie in einer anderen Tabelle (ZeroInventory) ein, in der Produkte ohne Bestand gespeichert werden.

USE AdventureWorks2012;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);
GO

INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)
SELECT ProductID, GETDATE()
FROM
(   MERGE Production.ProductInventory AS pi
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
           JOIN Sales.SalesOrderHeader AS soh
           ON sod.SalesOrderID = soh.SalesOrderID
           AND soh.OrderDate = '20070401'
           GROUP BY ProductID) AS src (ProductID, OrderQty)
    ON (pi.ProductID = src.ProductID)
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    WHEN MATCHED
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)
WHERE Action = 'DELETE';
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were inserted';
GO
SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;

Siehe auch

Verweis

BULK INSERT (Transact-SQL)

DELETE (Transact-SQL)

EXECUTE (Transact-SQL)

FROM (Transact-SQL)

IDENTITY-Eigenschaft (Transact-SQL)

NEWID (Transact-SQL)

SELECT (Transact-SQL)

UPDATE (Transact-SQL)

MERGE (Transact-SQL)

OUTPUT-Klausel (Transact-SQL)

Konzepte

Verwenden der Tabellen inserted und deleted