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 se podělí o své dlouholeté zkušenosti, aby poskytl několik rad o tom, co pro něj nejlépe fungovalo a jak můžete některé z těchto znalostí využít.
Často jsem se ocitl v situacích, kdy potřebuji chcete-li vložit záznamy do tabulky v sadě-založené provozu zabalené uvnitř transakce, kde sekundárně, a v rámci stejné transakce, jsem spawn-off následné vloží do souvisejících tabulek, kde musím projít-v klíčových hodnot, které byly výsledkem počáteční li VLOŽIT příkaz. Díky vylepšení Transact / SQL v SQL Serveru se to stalo mnohem jednodušší a lze to provést v jediném příkazu… BEZ SPOUŠTĚ!
řešení
jedním z vylepšení Transact / SQL v Microsoft SQL Server je výstupní dílčí klauzule příkazu INSERT. Nyní můžete zachytit záznamy vložené pomocí příkazu INSERT (myslím také, že je schopen zachytit sloupce IDENTITY hodnoty pro nové řádky) pro následné využití v další INSERT pro dítě tabulka přetrvávat referenční integrity bez nutnosti VLOŽIT spoušť.
proč nepoužít spoušť? Je to životaschopný a osvědčený konstrukt SQL Serveru, že?
krátká odpověď je “ ano, to je.“Spouštěče jsou však jedním z těch ošklivých malých tajemství, které databáze uchovává. Nemají jen skočit přímo na vás a říkají: „tady jsem!“Vezměte si například odstraňování potíží proces zablokování nebo ladění špatně-provádění dotazu – spoušť sedí v pozadí vystupuje jako je požádán, aby může být příčinou vašich problémů, ale budete projít mnoha iterací hledání uložené procedury, a ad-hoc T/SQL kódu, než budete pravděpodobně dokonce zastavit, aby zvážila, tam je spoušť vypálit data změny příkazy jazyka (DML) – VLOŽÍ, AKTUALIZACE nebo ODSTRANÍ, které jsou doplňkem k tomu, co se snažíte diagnostikovat. Spojuji použití triggerů s použitím ad-hoc T / SQL kódu používaného v zásobníku kódu aplikace a předaného instanci serveru SQL pro zpracování-postupy, které se vyhýbají.
proto se mi líbí to, co vidím s konstrukcí INSERT-OUTPUT. Získáte výhody, že budete moci zachytit vložené hodnoty, které pak můžete předat sekundárnímu příkazu – a to vše můžete zabalit do jediné transakce pro atomicitu. Syntaxe pro tento konstrukt je uvedena níže a liší se jen nepatrně od základního příkazu INSERT T / SQL:
INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column> --a další sloupce z SOME_TABLE pokud je třeba
DO SOME_OTHER_TABLE>
(
column_list>
)
VYBRAT
(
column_list>
)
Z source_table_OR_JOIN_of_multiple_tables>
KDE filtering_criteria>
jediný rozdíl mezi tímto a standardním INSERT je zařazení na VÝSTUPU…Do prohlášení. Aby to snadné myslíte, že jen jako sekundární INSERT uvnitř původní INSERT, který zachycuje hodnoty v virtualizovaných VLOŽENÉ tabulky – stejného stolu, že spoušť by použití – do procesu sekundární-li VLOŽIT do jiné tabulky. V níže uvedeném příkladu a v souladu s prázdninovou sezónou řekněme, že jste zodpovědní za trochu najímání v kancelářích společnosti AdventureWorks. Právo veselý starý elf je najal pro některé in-store akce a v souladu s firemní politiky, kterou vždy provést 90denní recenzi pro nové najímá. Chceme mít notifikaci zaznamenanou při zadávání nového pronájmu bez jakékoli další práce ze strany lidských zdrojů. Níže uvedený kód ukazuje, jak k tomu můžeme použít INSERT-OUTPUT.
USE AdventureWorks;
---Vytvořit Příklad Tabulky
/*
Poznámka: toto není plně normalizován. Pokud by se jednalo o skutečné řešení, zahrnul bych další tabulku
Pro typy oznámení.
také bych použil sloupec Int NotificationTypeID v tabulce oznámení
místo sloupce varchar(xx) NotificationType.
* /
Vytvořit autorizaci schématu dbo;
Jít
Vytvořit tabulku .
(
identita (1,1) not NULL,
VARCHAR(30) NOT NULL,
VARCHAR(30)NOT NULL,
omezení primárního klíče clusteru
(
ASC
) Na
) ON ;
Vytvořit tabulku .
(
IDENTITY(1,1) not NULL,
not NULL,
DATETIME not NULL,
VARCHAR(30) not NULL,
OMEZENÍ PRIMÁRNÍ KLÍČ CLUSTERED
(
ASC
)NA
);
Teď, když jsme stavěli objekty pro toto malé cvičení se můžeme podívat na VLOŽIT-VÝSTUP postavit v akci…
/*
Ukazují, jak lze vložit klíč přidané hodnoty na Zaměstnance.StaffID
do notifikací.StaffID v jedné transakci
* /
vložit do HR. Staff (křestní jméno, příjmení )
výstup vložen.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
DO HR.Oznámení
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Santa','Clause'); Výběr hned z obou Zaměstnanců a tabulky pro Oznámení uvidíte, že klíčové hodnoty byly úspěšně vstoupila do obou tabulek:
SELECT * FROM HR.Zaměstnanců;
SELECT * FROM HR.Oznámení; 
Nyní je velmi důležité – a docela omezující námitka k použití VLOŽIT-VÝSTUP. Výstupní cíl nemůže být součástí žádného cizího klíčového vztahu. I když neexistuje žádný kaskádový vztah k jinému objektu prostřednictvím tohoto vztahu v databázi. Podívejme se, co se stane, pokud ano. Do notifikace na StaffID přidáme cizí klíč, odkazujeme na sloupec StaffID v tabulce zaměstnanců a pokusíme se přidat další nápovědu k dovolené:
--Přidat Cizí Klíč pro StaffID sloupec Oznámení stolu
ALTER TABLE HR.Oznámení ADD CONSTRAINT
CIZÍ KLÍČ
(
StaffID
)
ODKAZY HR.Zaměstnanci
(
StaffID
);
/*
Ukazují, jak lze vložit klíč přidané hodnoty na Zaměstnance.StaffID
do notifikací.StaffID v jedné transakci
* /
vložit do HR. Staff (křestní jméno, příjmení )
výstup vložen.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
DO HR.Oznámení
(
StaffID,
NotificationDate,
NotificationType
)
VALUES ( 'Mrazivý','Sněhulák');
SELECT * FROM HR.Zaměstnanců;
SELECT * FROM HR.Oznámení;
následující chybová zpráva je vrácena podle očekávání:
Msg 332, ÚROVEŇ 16, Stav 1, Řádek 17
cílové TABULCE 'HR.Oznámení O VÝSTUP DO doložka nemůže být NA obou stranách (PRIMÁRNÍ KLÍČ, CIZÍ KLÍČ) vztah. Nalezeno referenční omezení 'FK_Notification_Staff'. to je pravděpodobně dobré v tomto případě, protože šance jsou dobré Pan Sněhulák nebude kolem 90 dny.
Další Kroky,
- Přečtěte si více o VÝSTUPNÍ klauzuli
- Také přečíst tento předchozí tip o VÝSTUPNÍ klauzuli
- Více tipů od autora jsou k dispozici prostřednictvím tohoto odkazu.
Poslední aktualizace: 2010-12-13
O autorovi
Tim Ford je Senior Správce Databáze s MindBody.Zobrazit všechny mé tipy
- další tipy pro vývojáře databází…