Die OUTPUT-Klausel wurde in SQL Server 2005 Version eingeführt. Die OUTPUT-Klausel gibt die Werte jeder Zeile zurück, die von einer INSERT-, UPDATE- oder DELETE-Anweisung betroffen war. Es unterstützt sogar die MERGE-Anweisung, die in SQL Server 2008 eingeführt wurde. Das Ergebnis der OUTPUT-Klausel kann während der Ausführung der Abfrage in eine separate Tabelle eingefügt werden. Diese Klausel wird am häufigsten zu Prüfungszwecken verwendet. Auf diese Weise kann jemand einen benutzerdefinierten Prozess implementieren, der wie CDC funktioniert, oder auf die gleiche Weise wie CDC handeln.

Darüber hinaus können die Ergebnisse der OUTPUT-Klausel an die Verarbeitungsanwendungen zurückgegeben werden, um sie in Bestätigungsmeldungen, Protokollierungen und anderen Anwendungsanforderungen zu verwenden. In diesem Artikel werde ich eine Reihe von Beispielen bereitstellen, um die Verwendung der OUTPUT-Klausel beim Erfassen der Ergebnisse der aktualisierten Zeilen in einer Tabellenvariablen für die UPDATE-Anweisungen zu veranschaulichen.

Erste Schritte

Um den folgenden Beispielen zu folgen, müssen Sie eine Tabelle Department_SRC erstellen, indem Sie den folgenden T-SQL-Code in einer SQL Server-Datenbank ausführen. Der referenzierte Abschnitt enthält Output_Update.sql-Datei, die den T-SQL-Code enthält, um die folgenden Beispiele auszuprobieren.

IF OBJECT_ID ('Department_SRC', 'U') IS NOT NULL DROP TABLE dbo.Department_SRC;CREATE TABLE .( IDENTITY(1,1) NOT NULL, varchar(50) NOT NULL, varchar(50) NOT NULL, NOT NULL) ON Insert into .(,,) Values('Engineering','Research and Development',getdate());

Sie können in den Tabellendaten sehen, dass der Datensatz in die Tabelle eingefügt wurde, wie in der folgenden Abbildung gezeigt.

Implementieren Sie die OUTPUT-Klausel in Update-Anweisungen

Wie wir in meinem vorherigen Artikel besprochen haben, gibt die OUTPUT-Klausel Zugriff auf zwei virtuelle Tabellen (Magic Tables):

1. „INSERTED“ enthält die neuen Zeilen SET)
2. „DELETED“ enthält die alte Kopie der Zeilen(UPDATE’s SET)

Die OUTPUT-Klausel mit einer UPDATE-Anweisung hat Zugriff auf die EINGEFÜGTEN und GELÖSCHTEN internen Tabellen. Das bedeutet, dass auf beide Tabellen gleichzeitig mit der Ausführung der UPDATE-Anweisung zugegriffen werden kann. Angenommen, wir aktualisieren einige Spalten in einer Tabelle für eine einzelne Zeile. Die neu aktualisierten Werte sind die Änderungen an der Tabelle und befinden sich in der eingefügten internen Tabelle. Außerdem sind die geänderten Daten die alten Datenwerte und werden in die GELÖSCHTE interne Tabelle eingefügt.

Anzeigen von Aktualisierungswerten

Mit der OUTPUT-Klausel können wir die aktualisierten Werte im Ausgabefenster anzeigen, indem wir die Spaltennamen mit dem eingefügten Präfix auswählen oder INSERTED .* um alle Spalten aus der EINGEFÜGTEN Tabelle anzuzeigen. Darüber hinaus können wir die alten Datenwerte aus der Tabelle im Ausgabefenster anzeigen, indem wir die Spaltennamen mit dem Präfix DELETED auswählen oder DELETED .* um alle Spalten aus dieser Tabelle anzuzeigen. Dies hilft auch beim Ausdrucken einiger Ausgabemeldungen im Management Studio-Fenster.

Um dies anhand eines Beispiels zu erklären, führe ich den folgenden Code aus, in dem wir versuchen, die Spalte GroupName vom Wert „Forschung und Entwicklung“ auf den Wert „IT“ für die Abteilung „Engineering“ zu aktualisieren.

--Update the GroupName for the Department.Update Set ='IT', ModifiedDate=Getdate()OUTPUT deleted.Name ,deleted.GroupName as GroupName_old, deleted.ModifiedDate as ModifiedDate_old, inserted.GroupName as GroupName_new, inserted.ModifiedDate as ModifiedDate_newWhere ='Engineering'

Sobald wir den obigen Code ausgeführt haben, können wir das Ergebnis der OUTPUT-Klausel sehen, wie im folgenden Bild gezeigt.

Einfügen von Ausgabeergebnissen in eine Tabellenvariable

Um dieses Beispiel zu veranschaulichen, habe ich eine Tabellenvariable namens @Updated deklariert, um die Ergebnisse der OUTPUT Klausel in dieser Tabellenvariablen zu speichern. Wir verwenden die OUTPUT INTO Syntax, um die Ergebnisse in einer Tabellenvariablen zu speichern. Bei diesem Ansatz können wir sowohl die neuen als auch die alten Werte, die von der Abfrage aktualisiert wurden, festlegen, die für die nächsten Schritte im selben Stapel verwendet werden können.

--capturing Updated values using table variable.DECLARE @Updated table( int, varchar(50), varchar(50), varchar(50), datetime, datetime);Update Set ='IT', ModifiedDate=Getdate()OUTPUT deleted.DepartmentID, deleted.Name, deleted.GroupName as GroupName_old, inserted.GroupName as GroupName_new, deleted.ModifiedDate as ModifiedDate_old,inserted.ModifiedDate as ModifiedDate_newINTO @UpdatedWhere ='Engineering'--Querying from @Updated output tableSelect * from @Updated

Sobald wir den obigen Code ausgeführt haben, können wir sehen, dass die Ergebnisse in die @Updated Tabellenvariable eingefügt wurden, wie im folgenden Bild gezeigt.

Verwendung der OUTPUT-Klausel zum Laden einer sich langsam ändernden Dimension vom TYP 3

Mit den oben gezeigten Beispielen ist die OUTPUT-Klausel eine sehr gute Alternative zum Laden einer Dimensionstabelle vom TYP 3. Um dies anhand eines Beispiels zu erklären, führe ich den folgenden Code aus, der eine Tabelle mit der Dimension TYP 3 namens Department_Type3 erstellt.

---Type 3 table exampleIF OBJECT_ID ('Department_Type3', 'U') IS NOT NULL DROP TABLE dbo.Department_Type3;CREATE TABLE .( IDENTITY(1,1) NOT NULL, varchar(50) NULL, varchar(50) NULL, varchar(50) NULL, NULL) ON GO---Insert some test valuesInsert into .(,,) Values('Engineering','Research and Development',getdate());Select * from .

Sobald wir den obigen Code ausführen, können wir das Ergebnis der Tabellendaten sehen, wie im folgenden Bild gezeigt.

Beim Einfügen eines neuen Datensatzes in die Tabelle Department_Type3 können wir sehen, dass die Spalte GroupName_old einen Nullwert und GroupName_Current den Wert der letzten Einfügung hat. Nehmen wir an, dass sich GroupName_current für den Abteilungsnamen „Engineering“ geändert hat und wir die Tabelle Department_Type3 mit dem neuen Wert GroupName_current aktualisieren müssen. Da es sich um eine TYP3-Dimensionstabelle handelt, ist es hier erforderlich, die Spalte GroupName_old mit „Forschung und Entwicklung“ und GroupName_Current mit dem neuen Wert zu aktualisieren.

--capturing Updated values using table variable.DECLARE @Updated table( int, varchar(50), varchar(50), varchar(50), datetime, datetime);Update Set ='IT', EffectiveDate=Getdate()OUTPUT deleted.DepartmentID, deleted.Name, deleted. as ,inserted.GroupName_current as GroupName_new, deleted.EffectiveDate as ModifiedDate_old,inserted.EffectiveDate as ModifiedDate_newINTO @UpdatedWhere ='Engineering'--Update the GroupName_old with old valuesUpdate aSet a.GroupName_old=b.GroupName_old, a.EffectiveDate=b.ModifiedDate_newfrom as ainner join @Updated as bon a.DepartmentID=b.DepartmentID--Querying the final tableSelect * from 

Sobald wir den obigen Code ausgeführt haben, erfassen wir die Ergebnisse aus der GELÖSCHTEN internen Tabelle in der Variablen @Updated table und verwenden diese Daten dann, um die endgültigen Tabellenspalten für die Implementierung dieser sich langsam ändernden Dimension vom TYP 3 zu aktualisieren. Wir können das Ergebnis der endgültigen Tabellendaten vor und nach der Ausführung der obigen Anweisung sehen, wie in den folgenden Bildern gezeigt.

Vor der Ausführung der UPDATE-Anweisung

Nach der Ausführung der UPDATE-Anweisung

Einschränkungen der Ausgabeklausel

Einige der Szenarien, in denen die Ausgabeklausel nicht unterstützt wird:

1. DML-Anweisungen, die auf lokale partitionierte Ansichten, verteilte partitionierte Ansichten oder entfernte Tabellen.
2. INSERT-Anweisungen, die eine EXECUTE-Anweisung enthalten.
3. Volltextprädikate sind in der OUTPUT-Klausel nicht zulässig, wenn die Datenbankkompatibilitätsstufe auf 100 festgelegt ist.
4. Die OUTPUT INTO-Klausel kann nicht zum Einfügen in eine View- oder Rowset-Funktion verwendet werden.
5. Eine benutzerdefinierte Funktion kann nicht erstellt werden, wenn sie eine OUTPUT INTO-Klausel enthält, deren Ziel eine Tabelle ist

Die vollständige Liste finden Sie unter diesem Link: https://technet.microsoft.com/en-us/library/ms177564(v=sql.110).aspx

Zusammenfassung

Wie in den obigen Beispielen gezeigt, können wir sehen, dass die OUTPUT Klausel einfach zu verwenden ist und viele benutzerdefinierte Codierungen vermeiden kann, um die Ausgabeergebnisse aktualisierter Werte mit alten und neuen Werten zu erfassen in eine Tabellenvariable während der Ausführung der Abfrage.