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 zal zijn vele jaren van ervaring te delen om een aantal tips over wat het beste voor hem heeft gewerkt en hoe u kunt gebruik maken van een deel van deze kennis te geven.
probleem
vaak kom ik in situaties terecht waarin ik records in een tabel moet invoegen in een setgebaseerde operatie die binnen een transactie is verpakt, waarbij ik in tweede instantie, en binnen dezelfde transactie, volgende inserts in gerelateerde tabellen moet uitpoepen waar ik sleutelwaarden moet doorgeven die het resultaat waren van het initiële INSERT-Commando. Dankzij een Transact / SQL verbetering in SQL Server, Dit is net veel gemakkelijker geworden en kan worden gedaan in een enkele statement… ZONDER TREKKER!
oplossing
een van de Transact / SQL-verbeteringen in Microsoft SQL Server is de sub-clausule voor uitvoer van het INSERT-statement. U kunt nu de records vastleggen die zijn ingevoegd via een insert-statement (denk ook dat u identiteitskolomwaarden kunt vastleggen voor de nieuwe rijen) voor later gebruik in een extra INSERT-statement voor een onderliggende tabel om de referentiële integriteit te behouden zonder dat er een insert-trigger nodig is.
waarom niet gewoon een trigger gebruiken? Het is een levensvatbare en bewezen constructie van SQL Server, toch?
het korte antwoord is ” ja, dat is het.”Triggers zijn echter een van die vervelende kleine geheimen die de database bewaart. Ze springen niet zomaar naar je toe en zeggen: “Hier ben ik!”Neem bijvoorbeeld het probleemoplossingsproces van deadlocks of het afstemmen van een slecht presterende query – een trigger zit in de achtergrond uitvoeren als het is gevraagd om kan het veroorzaken van uw problemen, maar je gaat gaan door vele iteraties van het zoeken opgeslagen procedures, en ad-hoc T/SQL – code voordat je waarschijnlijk zelfs stoppen om te overwegen is er een trigger afvuren van data modification language commands (DML) – INSERTS, UPDATES, of verwijdert die worden toegevoegd aan wat je probeert te diagnosticeren. Ik associeer het gebruik van triggers met het gebruik van ad-hoc T/SQL – code die wordt gebruikt in de codestack van een toepassing en die wordt doorgegeven aan een SQL Server-instantie voor verwerkingspraktijken om weg te blijven van.
daarom vind ik het leuk wat ik zie met de INSERT-OUTPUT constructie. U krijgt de voordelen van de mogelijkheid om de ingevoegde waarden vast te leggen die u vervolgens kunt doorgeven aan een secundaire opdracht – en u kunt dit allemaal in een enkele transactie verpakken voor atomiciteit. De syntaxis voor deze constructie wordt hieronder getoond en wijkt slechts weinig af van het basic INSERT T / SQL commando:
INSERT INTO SOME_TABLE>
(
column_list>
)
OUTPUT INSERTED.identity_column> --en de andere kolommen van SOME_TABLE indien nodig
IN SOME_OTHER_TABLE>
(
column_list>
)
SELECTEER
(
column_list>
)
UIT source_table_OR_JOIN_of_multiple_tables>
WAAR filtering_criteria>
Het enige verschil tussen dit en een standaard instructie INSERT is de opname op de UITGANG…In Verklaring. Om dit gemakkelijk te maken zie het als gewoon een secundair INSERT statement binnenin de originele INSERT statement dat de waarden in de gevirtualiseerde ingevoegde tabel vangt-dezelfde tabel die een trigger zou gebruiken-om een secundair INSERT naar een andere tabel te verwerken. In het onderstaande voorbeeld, en in overeenstemming met de feestdagen, laten we zeggen dat je verantwoordelijk bent voor het doen van een beetje van het huren op de corporate AdventureWorks kantoren. Een recht-jolly oude elf wordt ingehuurd voor een aantal in-store promoties en in overeenstemming met het bedrijfsbeleid u altijd het uitvoeren van een 90 dagen beoordeling voor nieuwe hires. Wij willen dat de notificatie wordt geregistreerd wanneer de nieuwe indienstneming wordt ingevoerd zonder extra werk van de kant van de menselijke hulpbronnen. De code hieronder laat zien hoe we INSERT-OUTPUT kunnen gebruiken om dit te doen.
gebruik AdventureWorks;
GO
---Maak voorbeeldtabellen
/*
Note, Dit is niet volledig genormaliseerd. Ik zou een andere tabel
Voor meldingstypen hebben opgenomen als dit een echte oplossing was.
Ik zou ook een kolom Int Notificatietype gebruiken in Notificatietabel
in plaats van een kolom varchar(xx) Notificatietype.
* /
SCHEMAAUTORISATIE DBO aanmaken;
ga
tabel aanmaken .
(
IDENTITY (1,1) NOT NULL,
VARCHAR(30) NOT NULL,
VARCHAR (30) NOT NULL,
CONSTRAINT primaire sleutel geclusterd
(
ASC
)ON
) ON ;
CREATE TABLE .
(
IDENTITY(1,1) NOT NULL,
NOT NULL,
DATETIME NOT NULL,
VARCHAR (30) NOT NULL,
CONSTRAINT primaire sleutel geclusterd
(
ASC
)ON
) ON ; nu we de objecten voor deze kleine oefening hebben gebouwd, kunnen we de INSERT-OUTPUT constructie in Actie bekijken…
/*
demonstreer hoe u de sleutelwaarden kunt invoegen die aan de notenbalk zijn toegevoegd.StaffID
In meldingen.StaffID in enkele transactie
* /
invoegen in HR. staf (voornaam, achternaam )
uitvoer ingevoegd.StaffID, DATEADD(d,90,GETDATE()),'90-Day Review'
in HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
waarden ( 'Santa','Claus'); nu selecteren uit zowel de notenbalk als de Notificatietabellen zult u zien dat de sleutelwaarden succesvol zijn ingevoerd in beide tabellen:
select * from HR.staff;
select * from HR.notification; 
nu is er een zeer belangrijke – en nogal beperkende waarschuwing voor het gebruik van INSERT-OUTPUT. Het Outputdoel kan geen deel uitmaken van een buitenlandse sleutelrelatie. Zelfs als er geen cascading relatie met een ander object via die relatie in de database. Laten we eens kijken wat er gebeurt als dat zo is. We voegen een buitenlandse sleutel toe aan Notification on StaffID, refererend aan de Staff kolom in de Staff tabel en proberen dan wat extra vakantie hulp toe te voegen:
--add Foreign Key for StaffID column to Notifications table
ALTER TABLE HR.Notification add CONSTRAINT
FOREIGN KEY
(
StaffID
)
referenties HR.Staff
(
StaffID
);
/*
demonstreer hoe u de sleutelwaarden kunt invoegen die aan personeel zijn toegevoegd.StaffID
In meldingen.StaffID in enkele transactie
* /
invoegen in HR. staf (voornaam, achternaam )
uitvoer ingevoegd.StaffID,DATEADD(d,90, GETDATE ()),'90-Day Review'
in HR.Notification
(
StaffID,
NotificationDate,
NotificationType
)
waarden ('Frosty', 'Snowman');
SELECT * FROM HR. Staff;
SELECT * FROM HR. Notification;
de volgende foutmelding wordt zoals verwacht geretourneerd:
MSG 332, niveau 16, Status 1, regel 17
de doeltabel 'HR.Notification' van de OUTPUT in clausule kan niet aan weerszijden van een (primaire sleutel, buitenlandse sleutel) relatie staan. Referentiebeperking 'FK_Notification_Staff'gevonden. Dit is waarschijnlijk goed in dit geval, omdat de kans groot is dat Mr.Snowman er over 90 dagen niet zal zijn.
volgende stappen
- Lees meer over de UITVOERCLAUSULE
- lees ook deze vorige tip over de uitvoerclausule
- meer tips van de auteur zijn beschikbaar via deze link.
Laatst Bijgewerkt: 2010-12-13
Over de auteur
Tim Ford is een Senior Database Administrator met MindBody.Bekijk al mijn tips
- meer tips voor Databaseontwikkelaars…