Using INSERT OUTPUT in a SQL Server Transaction
By: Tim Ford | Updated: 2010-12-13 | Comments (6) | Related: More > T-SQL
Free MSSQLTips Webinar: Development Best Practices for SQL Server
Attend this webinar to learn about development best practices for SQL Server. Andy Warren wird seine langjährige Erfahrung teilen, um einige Hinweise darauf zu geben, was für ihn am besten funktioniert hat und wie Sie etwas von diesem Wissen nutzen können.
Problem
Häufig befinde ich mich in Situationen, in denen ich Datensätze in einer mengenbasierten Operation in eine Tabelle einfügen muss, die in eine Transaktion eingeschlossen ist, in der ich sekundär und innerhalb derselben Transaktion nachfolgende Einfügungen in verwandte Tabellen erstelle, in denen ich Schlüsselwerte übergeben muss, die das Ergebnis des ersten Einfügebefehls waren. Dank einer Transact / SQL-Erweiterung in SQL Server wurde dies viel einfacher und kann in einer einzigen Anweisung ausgeführt werden… OHNE ABZUG!
Lösung
Eine der Transact/SQL-Erweiterungen in Microsoft SQL Server ist die OUTPUT-Unterklausel der INSERT-Anweisung. Sie können jetzt die über eine INSERT-Anweisung eingefügten Datensätze erfassen (denken Sie auch daran, Identitätsspaltenwerte für die neuen Zeilen erfassen zu können), um sie anschließend in einer zusätzlichen INSERT-Anweisung für eine untergeordnete Tabelle zu verwenden, um die referenzielle Integrität beizubehalten, ohne dass ein INSERT-Trigger erforderlich ist.
Warum nicht einfach einen Trigger verwenden? Es ist ein praktikables und bewährtes Konstrukt von SQL Server, oder?
Die kurze Antwort lautet: „Ja, das ist es.“ Trigger sind jedoch eines dieser fiesen kleinen Geheimnisse, die die Datenbank birgt. Sie springen nicht einfach direkt auf dich zu und sagen: „HIER BIN ICH!“ Nehmen Sie zum Beispiel den Fehlerbehebungsprozess von Deadlocks oder das Optimieren einer schlecht funktionierenden Abfrage – ein Trigger, der im Hintergrund so ausgeführt wird, wie er angefordert wurde, kann Ihre Probleme verursachen, aber Sie werden viele Iterationen der Suche nach gespeicherten Prozeduren durchlaufen und Ad-hoc-T / SQL-Code, bevor Sie wahrscheinlich sogar aufhören zu überlegen, ob es einen Trigger gibt, der Data Modification Language-Befehle (DML) auslöst – EINFÜGUNGEN, AKTUALISIERUNGEN oder LÖSCHUNGEN, die zu dem, was Sie diagnostizieren möchten, beitragen. Ich verbinde die Verwendung von Triggern mit der Verwendung von Ad-hoc-T / SQL-Code, der im Code-Stack einer Anwendung verwendet und zur Verarbeitung an eine SQL Server-Instanz übergeben wird.
Deshalb gefällt mir, was ich mit dem INSERT-OUTPUT-Konstrukt sehe. Sie haben den Vorteil, dass Sie die eingefügten Werte erfassen können, die Sie dann an einen sekundären Befehl übergeben können – und Sie können dies alles aus Atomaritätsgründen in eine einzelne Transaktion einschließen. Die Syntax für dieses Konstrukt ist unten dargestellt und unterscheidet sich nur geringfügig vom grundlegenden Befehl INSERT T / SQL:
INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column> --und andere Spalten von SOME_TABLE, wenn nötig
IN SOME_OTHER_TABLE>
(
column_list>
)
SELECT
(
column_list>
)
FROM source_table_OR_JOIN_of_multiple_tables>
WHERE filtering_criteria>
Der einzige Unterschied zwischen dieser und einer Standard-INSERT-Anweisung ist die Einbeziehung von am AUSGANG…IN Aussage. Um dies zu vereinfachen, stellen Sie sich einfach eine sekundäre INSERT-Anweisung innerhalb der ursprünglichen INSERT-Anweisung vor, die die Werte in der virtualisierten EINGEFÜGTEN Tabelle erfasst – dieselbe Tabelle, die ein Trigger verwenden würde -, um eine sekundäre EINFÜGUNG in eine andere Tabelle zu verarbeiten. Nehmen wir im folgenden Beispiel an, Sie sind für die Einstellung in den AdventureWorks-Büros des Unternehmens verantwortlich. Ein richtig lustiger alter Elf wird für einige Werbeaktionen im Geschäft eingestellt, und gemäß den Unternehmensrichtlinien führen Sie immer eine 90-tägige Überprüfung für Neueinstellungen durch. Wir möchten, dass die Notation bei der Neueinstellung ohne zusätzliche Personalarbeit erfasst wird. Der folgende Code zeigt, wie wir INSERT-OUTPUT verwenden können, um dies zu tun.
USE AdventureWorks;
GO
---Beispieltabellen erstellen
/*
Hinweis: Dies ist nicht vollständig normalisiert. Ich hätte eine andere Tabelle
für Benachrichtigungstypen eingefügt, wenn dies eine tatsächliche Lösung wäre.
Ich würde auch eine int NotificationTypeID Spalte in der Benachrichtigungstabelle
anstelle einer varchar(xx) NotificationType Spalte verwenden.
*/
SCHEMA-AUTORISIERUNG dbo ERSTELLEN;
GEHE ZU
TABELLE ERSTELLEN .
(
IDENTITÄT(1,1) NICHT NULL,
VARCHAR(30) NICHT NULL,
VARCHAR(30) NICHT NULL,
EINSCHRÄNKUNG PRIMÄRSCHLÜSSEL GECLUSTERT
(
ASC
)ON
) ON ;
TABELLE ERSTELLEN .
(
IDENTITY(1,1) NOT NULL,
NOT NULL,
DATETIME NOT NULL,
VARCHAR(30) NOT NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)ON
) ON ; Nachdem wir die Objekte für diese kleine Übung erstellt haben, können wir uns das Konstrukt INSERT-OUTPUT in Aktion ansehen…
/*
Zeigen Sie, wie Sie die Schlüsselwerte einfügen können, die dem Notensystem hinzugefügt wurden.StaffID
in Benachrichtigungen.StaffID in Einzeltransaktion
*/
EINFÜGEN IN HR.Staff ( Vorname, Nachname )
AUSGABE EINGEFÜGT.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Santa','Claus'); Wenn Sie nun aus den Tabellen Staff und Notification auswählen, sehen Sie, dass die Schlüsselwerte erfolgreich in beide Tabellen eingegeben wurden:
WÄHLEN SIE * AUS HR.Staff;
WÄHLEN SIE * AUS HR.Notification; 
Jetzt gibt es eine sehr wichtige – und ziemlich einschränkende Einschränkung bei der Verwendung der INSERT-AUSGABE. Das Ausgabeziel kann nicht Teil einer Fremdschlüsselbeziehung sein. Auch wenn über diese Beziehung in der Datenbank keine kaskadierende Beziehung zu einem anderen Objekt besteht. Schauen wir uns an, was passiert, wenn es so ist. Wir fügen der Benachrichtigung über StaffID einen Fremdschlüssel hinzu, verweisen auf die StaffID-Spalte in der Staff-Tabelle und versuchen dann, zusätzliche Urlaubshilfe hinzuzufügen:
--Add Foreign Key for StaffID column to Notifications table
ALTER TABLE HR.Notification ADD CONSTRAINT
FOREIGN KEY
(
StaffID
)
REFERENZEN HR.Staff
(
StaffID
);
/*
Zeigen Sie, wie Sie die zu Staff hinzugefügten Schlüsselwerte einfügen können.StaffID
in Benachrichtigungen.StaffID in Einzeltransaktion
*/
EINFÜGEN IN HR.Staff ( Vorname, Nachname )
AUSGABE EINGEFÜGT.StaffID, DATEADD(d,90,GETDATE()),'90-Tage-Überprüfung'
IN HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
WERTE ( 'Frostig','Schneemann');
SELECT * FROM HR.Staff;
SELECT * FROM HR.Notification;
Die folgende Fehlermeldung wird wie erwartet zurückgegeben:
Nachricht 332, EBENE 16, Zustand 1, Zeile 17
Die ZIELTABELLE 'HR.Notification' DER OUTPUT INTO-Klausel kann sich nicht AUF beiden Seiten einer (PRIMÄRSCHLÜSSEL-, FREMDSCHLÜSSEL-) Beziehung befinden. Referenzeinschränkung 'FK_Notification_Staff' gefunden. Dies ist in diesem Fall wahrscheinlich gut, da die Chancen gut stehen, dass Mr. Snowman in 90 Tagen nicht mehr da sein wird.
Nächste Schritte
- Lesen Sie mehr über die OUTPUT-Klausel
- Lesen Sie auch diesen vorherigen Tipp über die OUTPUT-Klausel
- Weitere Tipps des Autors finden Sie unter diesem Link.
Zuletzt aktualisiert: 13.12.2010
Über den Autor
Tim Ford ist Senior Datenbankadministrator bei MindBody.Alle meine Tipps anzeigen
- Weitere Tipps für Datenbankentwickler…