klauzula OUTPUT została wprowadzona w wersji SQL Server 2005. Klauzula OUTPUT zwraca wartości każdego wiersza, na który wpływ miały instrukcje INSERT, UPDATE lub DELETE. Obsługuje nawet polecenie MERGE, które zostało wprowadzone w SQL Server 2008. Wynik z klauzuli OUTPUT można wstawić do osobnej tabeli podczas wykonywania zapytania. Klauzula ta jest najczęściej stosowana do celów audytu. Używając tego, ktoś może zaimplementować niestandardowy proces, który działałby jak CDC lub działał w taki sam sposób jak CDC.

ponadto wyniki z klauzuli OUTPUT mogą być zwracane do aplikacji przetwarzających w celu wykorzystania w takich rzeczach jak wiadomości potwierdzające, logowanie i wszelkie inne wymagania aplikacji. W tym artykule przedstawię zestaw przykładów pokazujących użycie klauzuli OUTPUT w przechwytywaniu wyników zaktualizowanych wierszy do zmiennej tabeli dla instrukcji aktualizacji.

wprowadzenie

aby wykonać poniższe przykłady, musisz utworzyć tabelę Department_SRC, wykonując poniższy kod T-SQL w bazie danych SQL Server. Odnośna sekcja zawiera Output_Update.plik sql, który zawiera kod T-SQL, aby wypróbować poniższe przykłady.

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());

Możesz zobaczyć w tabeli DANE, że rekord został wstawiony do tabeli, jak pokazano na poniższym obrazku.

zaimplementuj klauzulę OUTPUT w instrukcjach aktualizacji

jak omówiliśmy w moim poprzednim artykule, klauzula OUTPUT daje dostęp do dwóch wirtualnych tabel (tabel magicznych):

1. „wstawione” zawiera nową rows (INSERT lub update ’ s set)
2. „deleted” zawiera starą kopię rows(update ’ s set)

Klauzula wyjściowa z instrukcją update będzie miała dostęp zarówno do wstawionych, jak i usuniętych tabel wewnętrznych. Oznacza to, że obie te tabele są dostępne jednocześnie z wykonaniem instrukcji UPDATE. Załóżmy na przykład, że aktualizujemy kilka kolumn w tabeli dla jednego wiersza. Nowo zaktualizowane wartości są zmianami w tabeli i znajdują się w wstawionej tabeli wewnętrznej. Ponadto dane, które zostały zmienione są starymi wartościami danych i są wstawiane do usuniętej tabeli wewnętrznej.

przeglądanie wartości aktualizacji

korzystając z klauzuli OUTPUT, możemy wyświetlić zaktualizowane wartości w oknie output, wybierając nazwy kolumn z prefiksem INSERTED lub używając INSERTED.* aby wyświetlić wszystkie kolumny z wstawionej tabeli. Ponadto możemy wyświetlić stare wartości danych z tabeli w oknie wyjściowym, wybierając nazwy kolumn z usuniętym prefiksem lub używając usuniętego.* aby wyświetlić wszystkie kolumny z tej tabeli. Pomoże to również w wydrukowaniu niektórych komunikatów wyjściowych w oknie management studio.

aby wyjaśnić to przykładem, wykonuję poniższy kod, w którym staramy się zaktualizować kolumnę GroupName z wartości „Research and Development” do wartości „IT” dla działu „Engineering”.

--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'

Po wykonaniu powyższego kodu możemy zobaczyć wynik klauzuli OUTPUT, jak pokazano na poniższym obrazku.

Wstawianie wyników wyjściowych do zmiennej Tabelkowej

aby zaprezentować ten przykład, zadeklarowałem zmienną tabelkową o nazwie @Updated, aby zapisać wyniki klauzuli wyjściowej do tej zmiennej tabelkowej. Używamy OUTPUT do składni w celu zapisania wyników do zmiennej tabelkowej. W tym podejściu mamy dostęp do ustawiania wierszy zarówno nowych wartości, jak i starych wartości, które zostały zaktualizowane przez zapytanie, które można wykorzystać do kolejnych kroków w tej samej partii.

--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

Po wykonaniu powyższego kodu widzimy, że wyniki zostały wstawione do zmiennej @Updated table, jak pokazano na poniższym obrazku.

wykorzystanie klauzuli wyjściowej do załadowania powoli zmieniającego się wymiaru typu 3

z powyższymi przykładami, klauzula wyjściowa jest bardzo dobrą alternatywą dla załadowania tabeli wymiarów typu 3. Aby wyjaśnić to przykładem, wykonuję poniższy kod, który utworzy tabelę wymiaru typu 3 o nazwie Department_Type3.

---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 .

Po wykonaniu powyższego kodu możemy zobaczyć wynik danych tabeli, jak pokazano na poniższym obrazku.

gdy wstawiamy nowy rekord do tabeli Department_Type3, widzimy, że kolumna GroupName_old ma wartość NULL, a GroupName_Current ma wartość ostatniego Wstawienia. Załóżmy, że nastąpiła zmiana nazwy działu „Engineering” w nazwie GroupName_current i musimy zaktualizować tabelę Department_Type3 o nową wartość GroupName_current. Ponieważ jest to tabela wymiarów typu 3, wymaganiem jest zaktualizowanie kolumny GroupName_old o „Research and Development” i GroupName_Current o nową wartość.

--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 

Po wykonaniu powyższego kodu, będziemy przechwytywać wyniki z usuniętej tabeli wewnętrznej do zmiennej @Updated table, a następnie używając tych danych do aktualizacji końcowych kolumn tabeli w celu implementacji tego powoli zmieniającego się wymiaru typu 3. Możemy zobaczyć wynik końcowych danych tabeli przed i po wykonaniu powyższej instrukcji, jak pokazano na poniższych obrazkach.

przed wykonaniem instrukcji aktualizacji

Po wykonaniu instrukcji aktualizacji

ograniczenia klauzuli wyjściowej

niektóre scenariusze, w których klauzula wyjściowa nie jest obsługiwana:

1. instrukcje DML odwołujące się do lokalnych widoków partycjonowanych, rozproszonych widoków partycjonowanych lub zdalnych tabel.
2. wstawia polecenia zawierające instrukcję EXECUTE.
3. pełnotekstowe predykaty nie są dozwolone w klauzuli OUTPUT, gdy poziom zgodności bazy danych jest ustawiony na 100.
4. klauzula output INTO nie może być użyta do wstawienia do widoku lub funkcji zestawu wierszy.
5. nie można utworzyć funkcji zdefiniowanej przez użytkownika, jeśli zawiera klauzulę output INTO, która ma tabelę jako cel

pełna lista znajduje się w tym linku: https://technet.microsoft.com/en-us/library/ms177564 (v=sql.110).aspx

podsumowanie

jak pokazano w powyższych przykładach, możemy zauważyć, że klauzula OUTPUT jest łatwa w użyciu i pozwala uniknąć wielu niestandardowych kodowań w celu przechwycenia wyników wyjściowych zaktualizowanych wartości zarówno ze starymi, jak i nowymi wartościami do zmiennej tabelarycznej podczas wykonywania zapytania.