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 va împărtăși mulți ani de experiență pentru a oferi câteva indicii cu privire la ceea ce a funcționat cel mai bine pentru el și cum puteți utiliza unele dintre aceste cunoștințe.
problemă
frecvent mă găsesc în situații în care am nevoie pentru a insera înregistrări într-un tabel într-o operațiune bazată pe set înfășurat în interiorul unei tranzacții în cazul în care în al doilea rând, și în cadrul aceleiași tranzacții, am spawn-off inserturi ulterioare în tabele conexe în cazul în care am nevoie pentru a trece-in valori cheie care au fost rezultatul comenzii INSERT inițială. Datorită unei îmbunătățiri Transact / SQL în SQL Server, acest lucru a devenit mult mai ușor și se poate face într-o singură declarație… FĂRĂ TRĂGACI!
soluție
una dintre îmbunătățirile Transact / SQL din Microsoft SQL Server este sub-clauza de ieșire a instrucțiunii INSERT. Acum Puteți captura înregistrările inserate printr-o instrucțiune INSERT (gândiți-vă, de asemenea, posibilitatea de a captura valorile coloanelor de identitate pentru noile rânduri) pentru utilizarea ulterioară într-o instrucțiune INSERT suplimentară pentru ca un tabel copil să persiste integritatea referențială fără a fi nevoie de un declanșator INSERT.
de ce nu folosiți doar un declanșator? Este o construcție viabilă și dovedită a SQL Server, nu?
răspunsul scurt este „da, este.”Cu toate acestea, declanșatoarele sunt unul dintre acele mici secrete urâte pe care le păstrează baza de date. Ei nu doar sari direct la tine și spune ” Iată-mă!”Luați, de exemplu, procesul de depanare a blocajelor sau reglarea unei interogări cu performanțe slabe – un declanșator așezat în fundal, care funcționează așa cum i s-a cerut, poate provoca problemele dvs., dar veți trece prin multe iterații de căutare a procedurilor stocate și a codului ad – hoc T/SQL înainte de a vă opri probabil să luați în considerare că există un declanșator care declanșează comenzile limbajului de modificare a datelor (DML) – inserții, actualizări sau ștergeri care sunt adjuvante la ceea ce încercați să diagnosticați. Asociez utilizarea declanșatoarelor cu utilizarea codului ad – hoc T/SQL utilizat în stiva de coduri a unei aplicații și trecut la o instanță SQL Server pentru practici de procesare de care să se ferească.
De aceea îmi place ceea ce văd cu constructul INSERT-OUTPUT. Beneficiați de posibilitatea de a capta valorile inserate pe care le puteți trece apoi la o comandă secundară – și puteți înfășura toate acestea într-o singură tranzacție pentru atomicitate. Sintaxa pentru acest construct este prezentată mai jos și diferă doar ușor de comanda de bază INSERT T / SQL:
INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column> --și alte coloane din SOME_TABLE dacă este necesar
în SOME_OTHER_TABLE>
(
column_list>
)
selectați
(
column_list>
)
din source_table_or_join_of_multiple_tables>
unde filtering_criteria>
singura diferență între aceasta și o declarație de inserare standard este includerea pe ieșire…În declarație. Pentru a face acest lucru ușor cred că de ea ca pur și simplu o instrucțiune INSERT secundar în interiorul instrucțiunii INSERT original, care surprinde valorile din tabelul inserat virtualizat – același tabel care ar folosi un declanșator – pentru a procesa o inserție secundar la un alt tabel. În exemplul de mai jos, și în conformitate cu sezonul de vacanță, să presupunem că sunteți responsabil pentru a face un pic de angajare la birourile Corporate AdventureWorks. Un elf vechi drept-vesel este angajat pentru unele promoții în magazin și în conformitate cu politica corporativă efectuați întotdeauna o revizuire 90 zi pentru orice noi angajari. Dorim ca notificarea să fie înregistrată atunci când noua închiriere este introdusă fără nicio muncă suplimentară din partea Resurselor Umane. Codul de mai jos demonstrează modul în care putem folosi INSERT-OUTPUT pentru a face acest lucru.
utilizați AdventureWorks;
GO
---creați tabele exemplu
/ *
Notă, Acest lucru nu este complet normalizat. Mi-ar fi inclus un alt tabel
pentru tipurile de notificare în cazul în care aceasta a fost o soluție reală.
aș folosi, de asemenea, o coloană Int NotificationTypeID în tabelul notificări
în loc de o coloană Varchar(xx) NotificationType.
*/
Creați schema de autorizare dbo;
Du-te
crearea tabelului .
(
identitate(1,1) nu NULL,
VARCHAR(30) nu NULL,
VARCHAR(30) nu NULL,
constrângere cheie primară grupate
(
ASC
)Pe
) pe;
crea tabel .
(
identitate(1,1) NOT NULL,
NOT NULL,
DATETIME NOT NULL,
VARCHAR(30) NOT NULL,
constrângere cheie primară grupate
(
ASC
)ON
) ON; acum, că am construit obiectele pentru acest mic exercițiu ne putem uita la constructul INSERT-OUTPUT în acțiune…
/ *
demonstrați cum puteți insera valorile cheie adăugate personalului.StaffID
În notificări.StaffID într-o singură tranzacție
*/
introduceți în HR. Staff (FirstName, LastName )
ieșire introdus.StaffID, Dateadd(d,90,GETDATE()),'revizuire de 90 de zile'
în HR.notificare
(
StaffID,
NotificationDate,
NotificationType
)
valori ( 'Moș Crăciun','Claus'); selectând acum atât din tabelele personal, cât și din cele de notificare, veți vedea că valorile cheie au fost introduse cu succes în ambele tabele:
selectați * din hr.personal;
selectați * din hr.notificare; 
acum există o avertizare foarte importantă – și destul de limitată la utilizarea INSERT-OUTPUT. Ținta de ieșire nu poate face parte din nicio relație cheie străină. Chiar dacă nu există nici o relație în cascadă la orice alt obiect prin această relație în baza de date. Să ne uităm la ce se întâmplă dacă este. Vom adăuga o cheie străină la notificare în StaffID, făcând referire la coloana StaffID din tabelul personal și apoi vom încerca să adăugăm ajutor suplimentar de vacanță:
- adăugați cheie străină pentru coloana StaffID la tabelul notificări
ALTER TABLE HR.notificare Adăugați constrângere
cheie străină
(
StaffID
)
referințe HR.Staff
(
StaffID
);
/*demonstrați cum puteți introduce valorile cheie adăugate personalului.StaffID
În notificări.StaffID într-o singură tranzacție
*/
introduceți în HR. Staff (FirstName, LastName )
ieșire introdus.StaffID, DATEADD (d, 90, GETDATE ()),'90-Day Review'
în HR.notificare
(
StaffID,
NotificationDate,
NotificationType
)
valori ('Frosty', 'Snowman');
selectați * din HR.Staff;
selectați * din HR.Notification;
următorul mesaj de eroare este returnat conform așteptărilor:
Msg 332, nivelul 16, Starea 1, Linia 17
tabelul țintă "HR.Notification" al clauzei OUTPUT INTO nu poate fi de ambele părți ale unei relații (cheie primară, cheie străină). S-au găsit constrângeri de referință 'FK_Notification_Staff'. Acest lucru este probabil bun în acest caz, deoarece șansele sunt bune Domnul Snowman nu va fi în jur de 90 de zile.
pașii următori
- citiți mai multe despre clauza de ieșire
- citiți și acest sfat anterior despre clauza de ieșire
- mai multe sfaturi de la autor sunt disponibile prin acest link.
Ultima actualizare: 2010-12-13
despre autor
Tim Ford este un administrator de baze de date senior cu mindbody.Vezi toate sfaturile mele
- Mai multe sfaturi pentru dezvoltatori de baze de date…