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 kommer att dela sina många års erfarenhet för att ge några tips om vad som har fungerat bäst för honom och hur du kan utnyttja en del av denna kunskap.
Problem
ofta befinner jag mig i situationer där jag behöver infoga poster i en tabell i en uppsättning baserad operation insvept inuti en transaktion där sekundärt och inom samma transaktion, jag spawn-off efterföljande insatser i relaterade tabeller där jag måste passera in nyckelvärden som var resultatet av den ursprungliga infoga kommandot. Tack vare en Transact / SQL-förbättring i SQL Server blev detta bara mycket enklare och kan göras i ett enda uttalande… UTAN UTLÖSARE!
lösning
en av Transact / SQL-förbättringarna i Microsoft SQL Server är OUTPUT-underklausulen i INSERT-satsen. Du kan nu fånga de poster som infogats via ett infoga-uttalande (tänk också att kunna fånga IDENTITETSKOLUMNVÄRDEN för de nya raderna) för efterföljande användning i ett extra infoga-uttalande för att en barntabell ska fortsätta referensintegritet utan att det behövs en infoga-utlösare.
varför inte bara använda en trigger? Det är en livskraftig och beprövad konstruktion av SQL Server, eller hur?
det korta svaret är ” ja, det är det.”Triggers är dock en av de otäcka små hemligheterna som databasen håller. De hoppar inte bara direkt ut på dig och säger ” här är jag!”Ta till exempel felsökningsprocessen för deadlocks eller ställa in en dåligt fungerande fråga-en utlösare som sitter i bakgrunden som den har blivit ombedd kan orsaka dina problem, men du kommer att gå igenom många iterationer av att söka lagrade procedurer och ad – hoc T/SQL-kod innan du förmodligen ens slutar överväga att det finns en utlösare som skjuter av data modification language commands (DML) – infogar, uppdateringar eller raderar som är tillägg till vad du försöker diagnostisera. Jag associerar användningen av triggers med användningen av ad-hoc T/SQL – kod som används i en applikations kodstack och skickas till en SQL Server-instans för bearbetningspraxis för att undvika.
det är därför jag gillar det jag ser med INSERT-OUTPUT-konstruktionen. Du får fördelarna med att kunna fånga in de infogade värdena som du sedan kan överföra till ett sekundärt kommando – och du kan linda in allt detta i en enda transaktion för atomicitet. Syntaxen för denna konstruktion visas nedan och skiljer sig bara något från basic INSERT T / SQL-kommandot:
INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column> --och andra kolumner från SOME_TABLE om det behövs
till SOME_OTHER_TABLE>
(
column_list>
)
välj
(
column_list>
)
från source_table_or_join_of_multiple_tables>
där filtering_criteria>
den enda skillnaden mellan detta och ett standardinsatsuttalande är inkluderingen på utgången…Till uttalande. För att göra det enkelt att tänka på det som helt enkelt ett sekundärt INFOGNINGSUTTALANDE inuti det ursprungliga INFOGNINGSUTTALANDET som fångar värdena i den virtualiserade infogade tabellen – samma tabell som en utlösare skulle använda – för att bearbeta en sekundär insats till en annan tabell. I exemplet nedan, och i enlighet med semesterperioden, låt oss säga att du är ansvarig för att göra lite anställning på företagets AdventureWorks kontor. En rätt jolly old elf anställs för vissa kampanjer i butiken och i enlighet med företagets policy utför du alltid en 90-dagars recension för nya anställningar. Vi vill ha notifieringen registrerad när den nya anställningen skrivs in utan ytterligare arbete från personalresurserna. Koden nedan visar hur vi kan använda INSERT-OUTPUT för att göra detta.
använd AdventureWorks;
GO
---skapa exempeltabeller
/ *
Obs, Detta är inte helt normaliserat. Jag skulle ha inkluderat en annan tabell
För Anmälningstyper om detta var en faktisk lösning.
Jag skulle också använda en int NotificationTypeID kolumn i Anmälningstabellen
i stället för en Varchar(xx) NotificationType kolumn.
* /
skapa schema tillstånd dbo;
gå
Skapa tabell .
(
identitet (1,1) inte NULL,
VARCHAR (30) Inte NULL,
VARCHAR(30)Inte NULL,
begränsning primär nyckel grupperade
(
ASC
) på
) på ;
Skapa tabell .
(
identitet (1,1) inte NULL,
inte NULL,
DATETIME inte NULL,
VARCHAR(30)Inte NULL,
begränsning primär nyckel grupperad
(
ASC
) på
) på ;
Nu när vi har byggt objekten för den här lilla övningen kan vi titta på INSERT-OUTPUT-konstruktionen i aktion…
/*
visa hur du kan infoga nyckelvärdena som läggs till personalen.StaffID
till anmälningar.StaffID i enstaka transaktioner
*/
Infoga i HR.personal ( förnamn, efternamn)
utgång infogad.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
värden ( 'Santa','Claus'); Om du väljer nu från både personal-och Anmälningstabellerna ser du att nyckelvärdena har matats in i båda tabellerna:
välj * från HR.personal;
välj * från HR.Notification; 
Nu finns det en mycket viktig-och ganska begränsande varning för att använda INSERT-OUTPUT. Utgångsmålet kan inte vara en del av något främmande nyckelförhållande. Även om det inte finns något kaskadförhållande till något annat objekt via det förhållandet i databasen. Låt oss titta på vad som händer om det är. Vi lägger till en främmande nyckel till anmälan om StaffID, hänvisar till StaffID-kolumnen i Personaltabellen och försöker sedan lägga till ytterligare semesterhjälp:
- Lägg till främmande nyckel för staffid-kolumnen i Meddelandetabellen
ALTER TABLE HR.meddelande Lägg till begränsning
utländsk nyckel
(
StaffID
)
referenser HR.personal
(
StaffID
);
/ *
visa hur du kan infoga nyckelvärdena som läggs till personalen.StaffID
till anmälningar.StaffID i enstaka transaktioner
*/
Infoga i HR.personal ( förnamn, efternamn)
utgång infogad.StaffID, DATEADD (d, 90,GETDATE()),'90-dagars recension'
till HR.anmälan
(
StaffID,
NotificationDate,
NotificationType
)
värden ('Frosty', 'Snowman');
välj * från HR. Staff;
välj * från HR. Notification;
följande felmeddelande returneras som förväntat:
Msg 332, nivå 16, stat 1, rad 17
måltabellen 'HR.Notification' av utdata i klausulen kan inte vara på någon sida av en (primärnyckel, utländsk nyckel) relation. Hittade REFERENSBEGRÄNSNING 'FK_Notification_Staff'. detta är förmodligen bra i det här fallet eftersom chansen är bra Mr. Snowman kommer inte att vara runt i 90 dagar.
nästa steg
- Läs mer om utgångsklausulen
- Läs också detta tidigare tips om utgångsklausulen
- fler tips från författaren finns tillgängliga via denna länk.
Senast uppdaterad: 2010-12-13
om författaren
tim Ford är en senior Databasadministratör med mindbody.Visa alla mina tips
- fler Databasutvecklartips…