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 jakaa hänen monen vuoden kokemus antaa joitakin viitteitä siitä, mikä on toiminut parhaiten hänelle ja miten voit hyödyntää joitakin tämän tiedon.
ongelma
usein joudun tilanteisiin, joissa minun täytyy lisätä tietueita taulukkoon set-pohjaisessa operaatiossa, joka on kääritty transaktion sisään, kun toissijaisesti ja samassa transaktiossa siirrän myöhemmät lisäykset vastaaviin taulukoihin, joissa minun on syötettävä avainarvoja, jotka olivat alkuperäisen INSERT-komennon tulos. Kiitos Transact/SQL lisälaite SQL Server, tämä vain tuli paljon helpompaa ja voidaan tehdä yhdessä lausumassa… ILMAN LIIPAISINTA!
ratkaisu
yksi Microsoft SQL Serverin Transact / SQL-parannuksista on INSERT-lausekkeen tuloste-alilauseke. Voit nyt tallentaa lisätyt tietueet INSERT-lausekkeen kautta (ajattele myös, että voit kaapata identiteetti-sarakkeen arvot uusille riveille) myöhempää käyttöä varten lisätaulukon insert-lausekkeessa, jotta viitteellinen eheys säilyy ilman INSERT-käynnistintä.
mikset vain käytä liipaisinta? Se on toimiva ja todistettu konstruktio SQL Server, eikö?
lyhyt vastaus on ” kyllä se on.”Kuitenkin, laukaisee ovat yksi niistä ilkeä pikku salaisuuksia, että tietokanta pitää. He eivät vain hyppää suoraan ulos ja sano: ”tässä minä olen!”Esimerkiksi vianmääritys prosessi umpikujaan tai tuning huonosti toimiva kysely-liipaisin istuu taustalla suorittaa, koska sitä on pyydetty voi aiheuttaa ongelmia, mutta aiot käydä läpi monia iteraatioita etsivät tallennettuja menettelyjä, ja ad-hoc T/SQL – koodi ennen luultavasti edes pysähtyä harkita on laukaista ampumisen pois tietojen muokkauskielen komentoja (DML) – insertit, päivitykset, tai poistaa, jotka ovat lisänä mitä yrität diagnosoida. Yhdistän käynnistimien käytön ad-hoc T/SQL – koodin käyttöön, jota käytetään sovelluksen koodipinossa ja siirretään SQL Server-instanssiin prosessointia varten-käytäntöjä, joita vältellä.
siksi pidän siitä, mitä näen insertti-ulostulo-konstruktiolla. Saat sen hyödyn, että voit tallentaa asetetut arvot, jotka voit siirtää toissijaiselle komennolle – ja voit kääriä tämän kaiken yhden tapahtuman sisään atomisuudeksi. Tämän Konstruktion syntaksi on esitetty alla ja se eroaa vain hieman basic INSERT T / SQL-komennosta:
INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column> --and other column from SOME_TABLE if need be
INTO SOME_OTHER_TABLE>
column_list>
)
SELECT
(
column_list>
)
from source_table_or_join_of_multiple_tables>
where filtering_criteria>
ainoa ero tämän ja standardi lisää lauseke on sisällyttäminen tulosteeseen…Lausunnoksi. Jotta tämä olisi helppoa ajatella, että se on yksinkertaisesti toissijainen insertti alkuperäisen INSERT-lausunnon sisällä, joka kaappaa virtualisoidun lisätyn taulukon arvot – saman taulukon, jota käynnistin käyttäisi-toisen lisäyksen käsittelyyn toiseen taulukkoon. Alla olevassa esimerkissä, ja sopusoinnussa lomakauden, sanotaan Olet vastuussa tehdä hieman palkata yritysten AdventureWorks toimistot. Right-hilpeä vanha tonttu palkataan joihinkin kaupan tarjouksiin, ja yrityspolitiikan mukaisesti suoritat aina 90 päivän katsauksen uusille palkkauksille. Haluamme, että notifikaatio kirjataan, kun uusi palkkaus otetaan käyttöön ilman, että Henkilöstövoimavaroilla on lisätyötä. Alla oleva koodi osoittaa, miten voimme käyttää INSERT-OUTPUT tehdä tämän.
USE AdventureWorks;
GO
---luo Esimerkkitaulukoita
/ *
Huomaa, että tämä ei ole täysin normalisoitunut. Olisin sisällyttänyt ilmoitustyypeille toisen taulukon
, Jos tämä olisi ollut varsinainen ratkaisu.
käyttäisin myös int NotificationTypeID-saraketta Ilmoitustaulukossa
varchar (xx) NotificationType-sarakkeen sijasta.
* /
CREATE SCHEMA AUTHORIZATION dbo;
GO
CREATE TABLE .
(
IDENTITY(1,1) NOT NULL,
VARCHAR(30) NOT NULL,
VARCHAR(30) NOT NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)on
) ON ;
CREATE TABLE .
(
IDENTITY(1,1) NOT NULL,
NOT NULL,
DATETIME NOT NULL,
VARCHAR(30)NOT NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
) on
) ON ; Now that we ’ ve built the objects for this little exercise we can look at the INSERT-OUTPUT construct in action…
/ *
Näytä, miten voit lisätä henkilöstöön lisätyt avainarvot.StaffID
Into Notifications.Staff in single transaction
* /
INSERT INTO HR. Staff (Etunimi, Sukunimi )
OUTPUT INSERTED.StaffID, DATEADD(d,90,GETDATE ()),"90-Day Review"
into HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ("Santa", "Claus"); valitsemalla nyt sekä henkilöstö-että Ilmoitustaulukoista näet, että keskeiset arvot syötettiin onnistuneesti molempiin taulukoihin:
select * from HR.staff;
select * from HR.notification; 
nyt INSERT – OUTPUT-lausekkeessa on erittäin tärkeä-ja melko rajoittava varoitus. Tulostavoite ei voi olla osa mitään ulkomaista avainsuhdetta. Vaikka tietokannassa ei ole ryöppyävää suhdetta mihinkään muuhun objektiin kyseisen suhteen kautta. Katsotaan, mitä tapahtuu, jos on. Lisäämme ulkomaisen avaimen Staffid-ilmoitukseen, joka viittaa staffid-sarakkeeseen henkilöstötaulukossa, ja yritämme sitten lisätä joitakin ylimääräisiä loma-apuja:
--Add Foreign Key for StaffID sarakkeen to Notifications table
ALTER TABLE HR.Notification ADD CONSTAINT
FOREIGN KEY
(
StaffID
)
REFERENCES HR.Staff
(
StaffID
);
/ *
Demonstrate how you can insert the key values added to Staff.StaffID
Into Notifications.Staff in single transaction
* /
INSERT INTO HR. Staff (Etunimi, Sukunimi )
OUTPUT INSERTED.StaffID,DATEADD(d,90, Getdate ()), "90-Day Review"
INTO HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ("Frosty", "Snowman");
SELECT * from HR. Staff;
SELECT * from HR. Notification;
seuraava virheilmoitus palautetaan odotetusti:
Msg 332, LEVEL 16, State 1, Line 17
kohdetaulukon "HR.Notification" lausekkeen tuloste ei voi olla kummallakaan puolella (ensisijainen avain, VIERASAVAIN) suhdetta. Löydetty viiterajoite "FK_Notification_Staff". tämä lienee tässä tapauksessa hyvä, sillä mahdollisuudet ovat hyvät, ettei Herra lumiukko ole 90 päivän päästä.
seuraavat vaiheet
- Lue lisää ULOSTULOLAUSEESTA
- Lue myös tämä edellinen vinkki ULOSTULOLAUSEESTA
- lisää kirjoittajan antamia vinkkejä löytyy tästä linkistä.
Viimeksi päivitetty: 2010-12-13
tekijästä
Tim Ford on mindbodyn vanhempi tietokantojen ylläpitäjä.Katso kaikki omat vinkkini
- lisää Tietokantakehittäjän vinkkejä…