Die SQL Server OUTPUT-Klausel gibt die Werte jeder Zeile zurück, die von einer INSERT-, UPDATE- oder DELETE-Anweisung betroffen war.
Die Output-Klausel gibt eine Kopie der Daten zurück, die während der Ausführung der Abfrage in eine separate Tabelle eingefügt werden können.
Diese Klausel wird am häufigsten verwendet, um Datenänderungen in der Tabelle für Auditzwecke zu verfolgen.
OUTPUT-Klausel gibt Zugriff auf zwei virtuelle Tabellen, wie unten angegeben.
- INSERTED , das die neuen Zeilen enthält (INSERT oder UPDATE)
- DELETED, das die alte Kopie der Zeilen enthält (leer für INSERT)
Die OUTPUT Klausel kann auf alle Spalten in der Tabelle zugreifen, auch wenn diese nicht Teil der Insert Anweisung sind .
SYNTAX
---- for insertINSERT INTO tablename OUTPUT inserted . *INTO @output_tablenameVALUES (col_value1 ,col_value2 ,..) ---for delete DELETE FROM tablenameOUTPUT deleted.* INTO @output_tablename WHERE Condition
CREATE TABLE dbo.Item (ItemId Int identity(1,1),ItemName Varchar(20),ItemPurchasedDate Datetime)
Output-Klausel mit Insert-Anweisung verwenden
Um das Ergebnis der Output-Klausel in eine Tabelle einzufügen, deklarieren Sie zuerst eine Tabellenvariable, um das Ergebnis der output-Klausel zu speichern ,und verwenden Sie die output caluse-Syntax, um das Ergebnis in der Tabellenvariablen zu speichern .
Um auf eingefügte Datensätze zuzugreifen, verwendet die Output Klausel die eingefügte virtuelle Tabelle, die die neuen Zeilen enthält.
In der folgenden Anweisung fügen wir einen Datensatz in die Item-Tabelle ein und verwenden die output-Klausel, um diese Datensätze in einer Tabellenvariablen @insert_log wie unten angegeben zu speichern
DECLARE @insert_log TABLE(ItemId Int, ItemName Varchar(20), ItemPurchasedDate Datetime )INSERT INTO dbo.Item OUTPUT inserted.ItemId, inserted.ItemName, inserted.ItemPurchasedDateINTO @insert_logVALUES ('Item1' , Getdate()) SELECT * FROM dbo.Item;SELECT * FROM @insert_log ;
ie können die Ausgabe von Item table und table variable sehen, Während Sie neue Datensätze in item table , ItemId ist nicht Teil der insert Anweisung . Sie können jedoch auf die Spalte ItemId in der OUTPUT-Klausel zugreifen.
Verwenden von Output caluse with Update Statement
Für den Zugriff auf aktualisierte Datensätze können Sie je nach Anforderung entweder inserted oder deleted virtual table with output klausel verwenden.
Wenn Sie auf alte Werte zugreifen möchten, die vor dem Aktualisieren des neuen Werts vorhanden waren, können Sie deleted virtual table und wenn Sie auf neue aktualisierte Werte zugreifen möchten, können Sie inserted virtual table with Output klausel .
In der folgenden Anweisung aktualisieren wir den ItemName-Wert in der Item-Tabelle und verwenden die output-Klausel, um die alten Werte für diese Datensätze in einer Tabellenvariablen @update_log zu speichern.
Da wir auf alte Datensätze zugreifen möchten, verwenden wir gelöschte virtuelle Tabellen.
DECLARE @update_log TABLE( ItemId int, ItemName Varchar(20), ItemPurchasedDate Datetime )UPDATE dbo.Item SET ItemName ='Item2'OUTPUT deleted.ItemId, deleted.ItemName, deleted.ItemPurchasedDateINTO @update_logWHERE ItemId =1SELECT * FROM dbo.Item;SELECT * FROM @update_log
Wie Sie sehen können, haben wir hier drei Ausgaben wie folgt
1: Es ist die Tabellenausgabe vor dem Aktualisieren der Datensätze.
2: Es ist die Tabellenausgabe nach dem Aktualisieren der Datensätze.
3: Es ist die von der Tabellenvariablen zurückgegebene Ausgabe, die die alten Datensatzwerte enthält, die vor der Aktualisierung waren.
Wenn Sie auf neue Datensätze zugreifen möchten, können Sie die eingefügte virtuelle Tabelle anstelle der gelöschten Tabelle verwenden.
Output-Klausel mit Delete-Anweisung
Die folgende Anweisung verwendet delete query, um den Datensatz für ItemId= 1 aus der Item-Tabelle zu löschen und die gelöschten Datensätze mithilfe von Output caluse in der Tabellenvariablen zu speichern.
Um auf die Ausgabeklausel für gelöschte Datensätze zuzugreifen, verwenden Sie deleted virtual table , die die alte Kopie der Zeilen enthält.
DECLARE @delete_log TABLE( ItemId Int, ItemName Varchar(20), ItemPurchasedDate Datetime )DELETE FROM dbo.Item OUTPUT deleted.ItemId, deleted.ItemName, deleted.ItemPurchasedDateINTO @delete_logWHERE ItemId =1SELECT * FROM dbo.Item;SELECT * FROM @delete_log
Wie Sie sehen können, sind in der Item-Tabelle keine Daten verfügbar, da sie gelöscht wurden, aber die output-Klausel fügt diese gelöschten Datensätze in eine Tabellenvariable @delete_log ein, was sehr nützlich ist, um gelöschte Datensätze aufzeichnungen.
Empfohlen für Sie
SQL Server Temporal Tables
822 Gesamtansichten, 8 Ansichten heute