Chciałem napisać kilka słów o klauzuli OUTPUT stosowanej w operacjach DML języka T-SQL. Wydaje mi się, że jest to często zapominana klauzula, a nawet stwierdzam, że wiele osób o niej nie wiem. Wielokrotnie widziałem, kod który miał za zadanie zalogować jakieś zmiant w tabeli. Często stosowano do tego odpowiednie rodzaje wyzwalaczy (triggerów), gdzie w trakcie jednej z operacji DML (Delete, Insert czy Update) wykorzystywane były tabele Inserted i /lub Deleted. SQL Server automatycznie zarządza tymi tabelami i udostępnia je podczas wykonywania wyzwalaczy
oraz operacji z klauzulą OUTPUT. Zależnie od tego, dla jakiej tabeli wykonujemy zmiany danych,
takie kolumny są dostępne w tabelach INSERTED i DELETED. Tabela INSERTED zawiera wiersze, które zostały
dodane do tabeli, natomiast DELETED te, które zostały usunięte. W przypadku operacji zmiany danych tabela
DELETED zawiera wartości przed zmianą, a tabela INSERTED wartości po zmianie.
I tutaj dochodzimy do klauzuli OUTPUT.
W trakcie wykonywania operacji modyfikacji danych (INSERT, UPDATE, DELETE) dodano dawno, dawno temu …. od SQL Server 2005, nową klauzulę OUTPUT, która umożliwia zwrócenie informacji o dodawanych, usuwanych lub modyfikowanych wierszach podczas ich wykonywania. Wyniki zwracane przez klauzulę OUTPUT można skierować do tabeli lub zmiennej tabelarycznej albo możemy jedynie zwrócić wybrane informacje (SELECT).
W kodzie poniżej tworzymy dwie bliźniacze tabele. W instrukcji INSERT dodajemy nowe rekordy do table1. Korzystając z klauzuli OUTPUT mamy dostęp do tabel udostępnianych na czas realizacji zapytania: inserted.* gdzie pobieramy dane i wstawiamy je do table2.
USE TestDB
GO
-- Creating two tables
CREATE TABLE Table1 (ID INT, Col VARCHAR(100))
CREATE TABLE Table2 (ID INT, Col VARCHAR(100))
-- Inserting into two tables together
INSERT INTO Table1 (ID, Col)
OUTPUT inserted.ID, inserted.Col
INTO Table2
VALUES(1,'First Value')
, (2, 'Second Value')
--Selecting from both the tables
SELECT 'Table1',* FROM Table1
SELECT 'Table2',* FROM Table2
-- Clean up
DROP TABLE Table1
DROP TABLE Table2
GO
W drugim kodzie wykonamy operacje insert i update, a dane będziemy zapisywać do zmiennej tabelarycznej.
USE TestDB
GO
-- Creating two tables
CREATE TABLE Table1 (ID INT, Col VARCHAR(100))
DECLARE @Table2 TABLE(ID_Insert INT, Col_Insert VARCHAR(100),ID_Deleted INT, Col_Deleted VARCHAR(100))
-- Inserting
INSERT INTO Table1 (ID, Col)
OUTPUT inserted.ID, inserted.Col
INTO @Table2(ID_Insert,Col_Insert)
VALUES(1,'First Value')
, (2, 'Second Value')
--Selecting
SELECT 'Table1',* FROM Table1
SELECT 'Table2',* FROM @Table2
UPDATE Table1
SET Col = 'NewValue'
OUTPUT Inserted.ID, Inserted.Col, Deleted.ID, Deleted.Col INTO @Table2
WHERE ID IN (1)
SELECT 'Table1',* FROM Table1
SELECT 'Table2',* FROM @Table2
-- Clean up
DROP TABLE IF Exists Table1
GO
