Articles

Using INSERT OUTPUT in a SQL Server Transaction

By: Tim Ford | Updated: 2010-12-13 | Comments (6) | Related: More > T-SQL

Development Best Practices for SQL Server

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren vil dele sine mange års erfaring for å gi noen tips om hva som har fungert best for ham, og hvordan du kan utnytte noe av denne kunnskapen.

Problem

ofte finner Jeg meg selv i situasjoner der jeg må sette inn poster i et bord i en set – basert operasjon innpakket inne i en transaksjon der sekundært, og innenfor samme transaksjon, jeg gyte-off påfølgende innlegg i relaterte tabeller der jeg må passere inn nøkkelverdier som var resultatet av den første INSERT-kommandoen. Takket Være En Transact / SQL-forbedring I SQL Server, ble dette bare mye lettere og kan gjøres i en enkelt setning… UTEN UTLØSER!

Løsning

En Av Transact / SQL-forbedringene I Microsoft SQL Server er UTDATA-underavsnittet I INSERT-setningen. Du kan nå fange opp postene som er satt inn via EN INSERT-setning (tenk også å kunne fange IDENTITETS kolonneverdier for de nye radene) for senere bruk i en ekstra INSERT-setning for en underordnet tabell for å fortsette referanseintegritet uten behov for EN INSERT-utløser.

Hvorfor ikke bare bruke en utløser? DET er en levedyktig og bevist konstruksjon AV SQL Server, ikke sant?

det korte svaret er » Ja, det er det.»Utløsere er imidlertid en av de ekle små hemmelighetene som databasen holder. De hopper ikke bare rett ut på deg og sier » HER ER jeg!»Ta for eksempel feilsøkingsprosessen med vranglåser eller tuning av en dårlig utførende spørring-en utløser som sitter i bakgrunnen som det er blitt bedt om å forårsake problemene dine, men du kommer til å gå gjennom mange iterasjoner av å søke lagrede prosedyrer, og ad hoc T / SQL-kode før du sannsynligvis til og med slutter å vurdere at det er en utløser som skyter av data modification language commands (DML) – INNLEGG, OPPDATERINGER ELLER SLETTER som er tillegg til det du prøver å diagnostisere. Jeg knytter bruken av utløsere til bruk av ad hoc T / SQL-kode som brukes i et programs kodestakk og sendes til EN SQL Server – forekomst for behandlingspraksis å skygge unna.

det er derfor jeg liker det jeg ser med INSERT-OUTPUT-konstruksjonen. Du får fordelene ved å kunne fange de innsatte verdiene som du deretter kan overføre til en sekundær kommando – og du kan pakke alt dette inn i en enkelt transaksjon for atomicity. Syntaksen for denne konstruksjonen er vist nedenfor og avviker bare litt fra basic INSERT T / SQL-kommandoen:

INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column> --og andre kolonner fra SOME_TABLE om nødvendig
TIL some_other_table>(
column_list>
)
VELG
(
column_list>
)
fra source_table_or_join_of_multiple_tables>

den eneste forskjellen mellom dette og en standard insert-setning er inkluderingen på utgangen…Til uttalelse. For å gjøre dette enkelt, tenk på det som bare en sekundær INSERT-setning inne i den opprinnelige INSERT-setningen som fanger verdiene i den virtualiserte INNSATTE tabellen – den samme tabellen som en utløser ville bruke-til å behandle en sekundær INNSATS i en annen tabell. I eksemplet nedenfor, og i tråd med høytiden, la oss si at du er ansvarlig for å gjøre litt ansettelse på Bedriftens AdventureWorks-kontorer. En riktig jolly gammel elf blir ansatt for noen kampanjer i butikken, og i tråd med bedriftens policy utfører du alltid en 90-dagers gjennomgang for eventuelle nye ansettelser. Vi ønsker å få notifikasjonen registrert når den nye leien er inngått uten ytterligere arbeid fra Menneskelige Ressurser. Koden nedenfor viser hvordan VI kan bruke INSERT-OUTPUT for å gjøre dette.

BRUK AdventureWorks;

---Lag Eksempel Tabeller
/ *
Merk, Dette er ikke fullt normalisert. Jeg ville ha tatt med et annet bord for Varslingstyper hvis dette var en faktisk løsning.
jeg vil også bruke en Int NotificationTypeID kolonne I Varslingstabellen
i stedet for en Varchar (xx) NotificationType kolonne.
*/
OPPRETT SKJEMA AUTORISASJON dbo;

OPPRETT TABELL .
IDENTITET (1,1) IKKE NULL,
VARCHAR (30) IKKE NULL,
VARCHAR (30) IKKE NULL,
BEGRENSNING PRIMÆRNØKKEL GRUPPERT
(
ASC
)på
) på;
OPPRETT TABELL.
IDENTITET (1,1) IKKE NULL,
IKKE NULL,
DATETIME IKKE NULL,
VARCHAR(30) IKKE NULL,
BEGRENSNING PRIMÆRNØKKEL GRUPPERT
(
ASC
)på
) på;

nå som vi har bygget objekter for denne lille øvelsen kan vi se på INSERT-OUTPUT konstruere i aksjon…

/ *
Demonstrer hvordan du kan sette inn nøkkelverdiene lagt Til Ansatte.StaffID
I Meldinger.StaffID i enkelt transaksjon * /
SETT INN HR. Staff (Fornavn, Etternavn)
UTGANG SATT inn.StaffID, DATEADD(d,90,GETDATE()),'90-Dagers Gjennomgang'
TIL HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VERDIER ( 'Santa','Claus');

Når du Velger nå Fra Både Staben og Varslingstabellene, ser du at nøkkelverdiene ble lagt inn i begge tabellene:

velg * fra hr.staff;
velg * FRA HR.Notification;

Transact/SQL-forbedringene I Microsoft SQL Server er OUTPUT-sub-setningsdelen I INSERT-setningen

Nå er det en veldig viktig-og ganske begrensende advarsel for å bruke INSERT-OUTPUT. Utgangsmålet kan ikke være en del av et fremmednøkkelforhold. Selv om det ikke er noen cascading forhold til noe annet objekt via det forholdet i databasen. La oss se på hva som skjer hvis det er. Vi legger til en sekundærnøkkel I Varsling På StaffID, refererer Til staffid-kolonnen i Stabstabellen og prøver deretter å legge til litt ekstra feriehjelp:

- Legg Til Sekundærnøkkel For staffid-kolonnen I Notifications table
ALTER TABLE HR. Notification LEGG TIL BEGRENSNING
SEKUNDÆRNØKKEL
(
StaffID
)
REFERANSER HR.Staff
(
StaffID
);
/*
Demonstrer hvordan du kan sette inn nøkkelverdiene lagt Til Ansatte.StaffID
I Meldinger.StaffID i enkelt transaksjon * /
SETT INN HR. Staff (Fornavn, Etternavn)
UTGANG SATT inn.StaffID,DATEADD(d,90, GETDATE ()),'90-Dagers Gjennomgang'
TIL HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VERDIER ('Frosty', 'Snømann');
VELG * FRA HR.Staff;
VELG * FRA HR.Notification;

følgende feilmelding returneres som forventet:

Msg 332, NIVÅ 16, Stat 1, Linje 17
MÅLTABELLEN 'HR. Notification' AV OUTPUT INTO-klausulen kan ikke være på hver side av et (PRIMÆRNØKKEL, FREMMEDNØKKEL) forhold. Fant referansebegrensning 'FK_Notification_Staff'.

Dette er trolig bra i dette tilfellet Siden sjansene Er gode Mr. Snowman ikke kommer til å være rundt i 90 dager.

Neste Trinn
  • Les mer om OUTPUT-klausulen
  • les også dette forrige tipset om OUTPUT-klausulen
  • Flere tips fra forfatteren er tilgjengelige via denne lenken.

Sist Oppdatert: 2010-12-13

få skript

neste tips knapp

om forfatteren
mssqltips forfatter tim ford tim ford er en senior database administrator med mindbody.
Vis alle mine tips
Relaterte Ressurser

  • Flere Database Utvikler Tips…

Legg igjen en kommentar

Din e-postadresse vil ikke bli publisert. Obligatoriske felt er merket med *