Using INSERT OUTPUT in a SQL Server Transaction
By: Tim Ford | Updated: 2010-12-13 | Comments (6) | Related: More > T-SQL
Free MSSQLTips Webinar: Development Best Practices for SQL Server
Attend this webinar to learn about development best practices for SQL Server. Andy Warren podzieli się swoim wieloletnim doświadczeniem, aby dać kilka wskazówek na temat tego, co działało najlepiej dla niego i jak można wykorzystać niektóre z tej wiedzy.
Problem
często znajduję się w sytuacjach, w których muszę wstawić rekordy do tabeli w operacji opartej na zestawach zawiniętych wewnątrz transakcji, gdzie wtórnie, w ramach tej samej transakcji, odradzam kolejne wstawki do powiązanych tabel, w których muszę przekazać wartości kluczy, które były wynikiem początkowego polecenia INSERT. Dzięki ulepszeniu Transact / SQL w SQL Server, stało się to znacznie łatwiejsze i można to zrobić w jednej instrukcji… BEZ SPUSTU!
rozwiązanie
jednym z ulepszeń Transact / SQL w Microsoft SQL Server jest podpunkt wyjścia instrukcji INSERT. Możesz teraz przechwytywać rekordy wstawione za pomocą instrukcji INSERT (pomyśl, że można również przechwytywać wartości kolumn tożsamości dla nowych wierszy) do późniejszego wykorzystania w dodatkowej instrukcji INSERT dla tabeli podrzędnej w celu zachowania integralności odniesienia bez potrzeby wyzwalania wstawiania.
Dlaczego po prostu nie użyć spustu? To realna i sprawdzona konstrukcja SQL Server, prawda?
krótka odpowiedź brzmi ” tak, jest.”Jednak wyzwalacze są jednym z tych paskudnych małych sekretów, które przechowuje baza danych. Nie wyskakują na Ciebie i nie mówią: „Oto jestem!”Weźmy na przykład proces rozwiązywania problemów związanych z blokadami lub strojeniem źle wykonującego zapytania-WYZWALACZ siedzący w tle, wykonujący tak, jak został poproszony, może powodować problemy, ale przejdziesz wiele iteracji przeszukiwania procedur składowanych i kodu ad – hoc T/SQL, zanim prawdopodobnie przestaniesz rozważać, że istnieje WYZWALACZ odpalający polecenia języka modyfikacji danych (DML)-wstawia, aktualizuje lub usuwa, które są dodatkowe do tego, co próbujesz zdiagnozować. Kojarzy mi się użycie wyzwalaczy z użyciem kodu ad-hoc T/SQL używanego w stosie kodu aplikacji i przekazywanego do instancji SQL Server w celu uniknięcia praktyk przetwarzania.
dlatego podoba mi się to, co widzę z konstrukcją INSERT-OUTPUT. Zyskujesz korzyści wynikające z przechwytywania wstawionych wartości, które możesz następnie przekazać drugorzędnemu poleceniu – i możesz zawinąć to wszystko w jedną transakcję w celu uzyskania atomowości. Składnia tej konstrukcji jest pokazana poniżej i różni się tylko nieznacznie od podstawowego polecenia INSERT T / SQL:
INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column> --I inne kolumny z SOME_TABLE, jeśli zajdzie taka potrzeba
do SOME_OTHER_TABLE>
(
column_list>
)
Wybierz
(
column_list>
)
from source_table_or_join_of_multiple_tables>
Where filtering_criteria>
jedyna różnica między tym a standardową instrukcją Insert jest włączenie na wyjściu…Do oświadczenia. Aby to ułatwić, pomyśl o tym jako o wtórnej instrukcji INSERT wewnątrz oryginalnej instrukcji INSERT, która przechwytuje wartości w zwirtualizowanej wstawionej tabeli – tej samej tabeli, której użyłby wyzwalacz-do przetworzenia wtórnego wstawienia do innej tabeli. W poniższym przykładzie, zgodnie z sezonem wakacyjnym, Załóżmy, że jesteś odpowiedzialny za zatrudnienie w biurach Corporate AdventureWorks. Dobry stary elf jest zatrudniany do promocji w sklepie i zgodnie z polityką korporacyjną zawsze wykonujesz przegląd 90 dni dla nowych pracowników. Chcemy, aby notyfikacja była rejestrowana przy wprowadzaniu nowego zatrudnienia bez dodatkowej pracy ze strony zasobów ludzkich. Poniższy kod pokazuje, jak możemy użyć INSERT-OUTPUT, aby to zrobić.
użyj AdventureWorks;
przejdź
---Utwórz przykładowe tabele
/*
uwaga, nie jest to w pełni znormalizowane. Dodałbym inną tabelę
dla typów powiadomień, gdyby było to rzeczywiste rozwiązanie.
użyłbym również kolumny Int NotificationTypeID w tabeli powiadomień
zamiast kolumny VARCHAR(xx) NotificationType.
*/
Utwórz schemat autoryzacji dbo;
przejdź
Utwórz tabelę .
(
IDENTITY(1,1) NOT NULL,
VARCHAR(30) NOT NULL,
VARCHAR(30) NOT NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)ON
) ON;
Utwórz tabelę .
(
IDENTITY(1,1) NOT NULL,
NOT null,
DATETIME NOT NULL,
VARCHAR(30) NOT NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)ON
) ON; teraz, gdy zbudowaliśmy obiekty do tego małego ćwiczenia, możemy spojrzeć na konstrukcję INSERT-OUTPUT w akcji…
/*
StaffID
do powiadomień.StaffID w pojedynczej transakcji
*/
wstaw do hr. Staff (FirstName, LastName )
wstawione wyjście.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
do hr. Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Santa','Claus'); wybierając teraz z tabel pięciolinii i powiadomień, zobaczysz, że wartości klucza zostały pomyślnie wprowadzone do obu tabel:
select * from hr. staff;
select * from hr. notification; 
teraz istnieje bardzo ważne-i dość ograniczające zastrzeżenie do użycia INSERT – OUTPUT. Cel wyjściowy nie może być częścią żadnej relacji klucza obcego. Nawet jeśli nie ma kaskadowej relacji z jakimkolwiek innym obiektem poprzez tę relację w bazie danych. Zobaczmy, co się stanie, jeśli tak jest. Dodamy klucz obcy do powiadomienia o StaffID, odwołując się do kolumny StaffID w tabeli Staff, a następnie spróbujemy dodać dodatkową pomoc wakacyjną:
--Add Foreign Key for StaffID column to Notifications table
ALTER TABLE HR.Notification ADD CONSTRAINT
FOREIGN KEY
(
StaffID
)
REFERENCES HR.Staff
(
StaffID
);
/*
Pokaż, jak możesz wstawić wartości klucza dodane do pięciolinii.StaffID
do powiadomień.StaffID w pojedynczej transakcji
*/
wstaw do hr. Staff (FirstName, LastName )
wstawione wyjście.StaffID,DATEADD(d,90, GETDATE ()),'90-Day Review'
do hr. Notification
(
StaffID,
NotificationDate,
NotificationType
)
wartości ( 'Frosty','Snowman');
SELECT * FROM HR.Staff;
SELECT * FROM HR.Notification;
następujący komunikat o błędzie jest zwracany zgodnie z oczekiwaniami:
Msg 332, LEVEL 16, State 1, Line 17
docelowa tabela 'HR.Notification' klauzuli OUTPUT INTO nie może znajdować się po obu stronach relacji (klucz podstawowy, klucz obcy). Znaleziono ograniczenie odniesienia 'FK_Notification_Staff'. to chyba dobrze w tym przypadku, bo szanse są dobre Pan bałwanek nie będzie za 90 dni.
kolejne kroki
- Przeczytaj więcej o klauzuli wyjściowej
- przeczytaj również poprzednią poradę o klauzuli wyjściowej
- Więcej wskazówek od autora jest dostępnych pod tym linkiem.
Ostatnia aktualizacja: 2010-12-13
o autorze
Tim Ford jest starszym administratorem bazy danych z MINDBODY.Zobacz wszystkie moje porady
- Więcej porad dla programistów baz danych…