Polecenie output – odczyt informacji o przetworzonych rekordach

2012-09-01

Od wersji SQL Server 2005 w języku T-SQL dostępne jest polecenie output. Możemy dołączyć je do instrukcji insert, update, delete lub merge w celu uzyskania informacji o rekordach przetworzonych w wyniku ich działania. Informacje te udostępniane są przez dwa zbiory danych: inserted oraz deleted – ich struktura odpowiada tabeli, na której wykonywana jest dana operacja. Zbiór inserted dostępny jest przy operacjach insert, update oraz merge i zawiera nową (zaktualizowaną) postać rekordów. Z kolei zbiór deleted udostępniany jest przy poleceniach update, delete oraz merge i przechowuje oryginalną postać rekordów. Mamy dwie możliwości wykorzystania polecenia output, możemy jedynie zwrócić wybrane informacje lub poprzez klauzulę into umieścić je w określonej tabeli lub zmiennej tabelarycznej.
Aby zaprezentować działanie omawianego polecenia stworzyłem dwie tabele: #Test, na której przeprowadzane będą określone operacje oraz #Log, w której zapisywana będzie ich historia:

create table #Test
(
	Id int Identity(1, 1),
	Name varchar(50)
)

create table #Log
(
	Id int,
	NameOld varchar(50),
	NameNew varchar(50),
	OperationType varchar(6),
	OperationTime datetime
)

Poniższy kod wstawia do tabeli #Test cztery rekordy i przy użyciu polecenia output informacje o nich umieszcza w tabeli #Log (jawne specyfikowanie pól tabeli docelowej nie jest konieczne):

insert into #Test(Name)
    output inserted.Id, inserted.Name, 'INSERT', GetDate()
    into #Log(Id, NameNew, OperationType, OperationTime)
values ('Name_1'), ('Name_2'), ('Name_3'), ('Name_4')

Oto zawartość tabel #Test oraz #Log:

Jak widać instrukcja output umożliwia odczyt wartości z pól zdefiniowanych jako identity.
Kolejny przykład dotyczy modyfikacji rekordów:

update #Test set Name = 'Name_44'
	output inserted.Id, inserted.Name, deleted.Name, 'UPDATE', GetDate()
	into #Log(Id, NameNew, NameOld, OperationType, OperationTime)
where Name = 'Name_4'

Zawartość tabel #Test oraz #Log:

Usuwanie rekordów:

delete from #Test
	output deleted.Id, deleted.Name, 'DELETE', GetDate()
	into #Log(Id, NameOld, OperationType, OperationTime)
where Name in ('Name_2', 'Name_3')

Aktualna zawartość tabel #Test oraz #Log:

W przypadku polecenia merge klauzula output dysponuje dodatkowym argumentem $action typu nvarchar(10). Argument ten udostępnia informacje o rodzaju wykonanej na danym rekordzie operacji (‚INSERT’, ‚UPDATE’ lub ‚DELETE’):

merge #Test as t
using
(
	select v.Id, v.Name
	from (values (1, 'Name_11'), (2, 'Name_55')) v(Id, Name)
) as s on s.Id = t.Id
when matched and t.Name <> s.Name then update set t.Name = s.Name
when not matched by target then insert (Name) values (s.Name)
when not matched by source then delete
output $action, deleted.Id as deletedId, deleted.Name as deteledName,
	inserted.Id as insertedId, inserted.Name as insertedName;

Oto zbiór zwrócony przez polecenie output oraz ostateczna postać tabeli #Test:

Więcej informacji na temat polecenia output: MSDN

Reklamy

Posted on 2012-09-01, in SQL Server and tagged , , . Bookmark the permalink. Dodaj komentarz.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Log Out / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Log Out / Zmień )

Facebook photo

Komentujesz korzystając z konta Facebook. Log Out / Zmień )

Google+ photo

Komentujesz korzystając z konta Google+. Log Out / Zmień )

Connecting to %s

%d blogerów lubi to: