De OUTPUT clausule werd geïntroduceerd in SQL Server 2005 versie. De OUTPUTCLAUSULE retourneert de waarden van elke rij die werd beïnvloed door een INSERT -, UPDATE-of DELETE-statements. Het ondersteunt zelfs het MERGE statement, dat werd geà ntroduceerd in SQL Server 2008. Het resultaat van de output clausule kan in een aparte tabel worden ingevoegd tijdens het uitvoeren van de query. Deze clausule wordt het meest gebruikt voor auditdoeleinden. Door dit te gebruiken kan iemand een aangepast proces implementeren dat zou werken als CDC, of handelen op dezelfde manier als CDC.

bovendien kunnen de resultaten van de OUTPUTCLAUSULE worden geretourneerd naar de verwerkingsapplicaties voor gebruik in bijvoorbeeld bevestigingsberichten, logging en andere toepassingsvereisten. In dit artikel zal ik een aantal voorbeelden geven om het gebruik van de OUTPUTCLAUSULE te laten zien bij het vastleggen van de resultaten van de bijgewerkte rijen in een tabelvariabele voor de UPDATEVERKLARINGEN.

aan de slag

om de onderstaande voorbeelden te volgen, moet u een Table Department_SRC aanmaken door de onderstaande T-SQL-code uit te voeren in een SQL Server-Database. De gerefereerde sectie bevat Output_Update.SQL-bestand, dat de T-SQL-code bevat om de onderstaande voorbeelden uit te proberen.

IF OBJECT_ID ('Department_SRC', 'U') IS NOT NULL DROP TABLE dbo.Department_SRC;CREATE TABLE .( IDENTITY(1,1) NOT NULL, varchar(50) NOT NULL, varchar(50) NOT NULL, NOT NULL) ON Insert into .(,,) Values('Engineering','Research and Development',getdate());

u kunt in de tabel zien dat de record is ingevoegd in de tabel, zoals weergegeven in de onderstaande afbeelding.

implementeer de output clausule in Update Statements

zoals besproken in mijn vorige artikel, geeft de output clausule toegang tot twee virtuele tabellen (magische tabellen):

1. “ingevoegd” bevat de nieuwe rijen (invoegen of bijwerken SET)
2. “deleted” bevat de oude kopie van de rijen(update ‘ s set)

De UITVOERCLAUSULE met een Update Statement heeft toegang tot zowel de ingevoegde als verwijderde interne tabellen. Dat betekent dat beide tabellen toegankelijk zijn op hetzelfde moment van het uitvoeren van de update statement. Stel bijvoorbeeld dat we een paar kolommen in een tabel bijwerken voor een enkele rij. De nieuw bijgewerkte waarden zijn de wijzigingen in de tabel en zijn in de ingevoegde interne tabel. Bovendien zijn de gegevens die zijn gewijzigd de oude gegevenswaarden en worden ingevoegd in de verwijderde interne tabel.

Updatewaarden weergeven

met behulp van de outputclausule kunnen we de bijgewerkte waarden in het uitvoervenster weergeven door de kolomnamen met het ingevoegde voorvoegsel te selecteren of door ingevoegd te gebruiken.* om alle kolommen uit de ingevoegde tabel weer te geven. Bovendien kunnen we de oude gegevenswaarden uit de tabel in het uitvoervenster weergeven door de kolomnamen met verwijderd voorvoegsel te selecteren of verwijderd te gebruiken.* om alle kolommen van die tabel weer te geven. Dit zal ook helpen bij het afdrukken van sommige uitvoerberichten in het Management studio-venster.

om dit met een voorbeeld uit te leggen, voer ik de onderstaande code uit, waarin we proberen de kolom Groupnaam bij te werken van de waarde “onderzoek en ontwikkeling” naar de waarde “IT” voor de afdeling “Engineering”.

--Update the GroupName for the Department.Update Set ='IT', ModifiedDate=Getdate()OUTPUT deleted.Name ,deleted.GroupName as GroupName_old, deleted.ModifiedDate as ModifiedDate_old, inserted.GroupName as GroupName_new, inserted.ModifiedDate as ModifiedDate_newWhere ='Engineering'

zodra we de bovenstaande code uitvoeren, kunnen we het resultaat van de output clausule zien zoals weergegeven in de onderstaande afbeelding.

uitvoerresultaten invoegen in een tabel variabele

om dit voorbeeld te laten zien, heb ik een tabel variabele genaamd @Updated gedeclareerd om de resultaten van de output clausule in deze tabel variabele op te slaan. We gebruiken de uitvoer in de syntaxis om de resultaten op te slaan in een tabel variabele. In deze aanpak hebben we toegang tot rijen zowel de nieuwe waarden en oude waarden die zijn bijgewerkt door de query, die kunnen worden gebruikt voor de volgende stappen in dezelfde batch.

--capturing Updated values using table variable.DECLARE @Updated table( int, varchar(50), varchar(50), varchar(50), datetime, datetime);Update Set ='IT', ModifiedDate=Getdate()OUTPUT deleted.DepartmentID, deleted.Name, deleted.GroupName as GroupName_old, inserted.GroupName as GroupName_new, deleted.ModifiedDate as ModifiedDate_old,inserted.ModifiedDate as ModifiedDate_newINTO @UpdatedWhere ='Engineering'--Querying from @Updated output tableSelect * from @Updated

zodra we de bovenstaande code uitvoeren, kunnen we zien dat de resultaten zijn ingevoegd in de @Updated table variabele zoals weergegeven in de onderstaande afbeelding.

gebruik van de Outputclausule om een langzaam veranderende dimensie van TYPE 3 te laden

met de hierboven getoonde voorbeelden is de outputclausule een zeer goed alternatief voor het laden van een dimensietabel van TYPE 3. Om dit met een voorbeeld uit te leggen, voer ik de onderstaande code uit, die een tabel van type 3 dimensie maakt genaamd Department_Type3.

---Type 3 table exampleIF OBJECT_ID ('Department_Type3', 'U') IS NOT NULL DROP TABLE dbo.Department_Type3;CREATE TABLE .( IDENTITY(1,1) NOT NULL, varchar(50) NULL, varchar(50) NULL, varchar(50) NULL, NULL) ON GO---Insert some test valuesInsert into .(,,) Values('Engineering','Research and Development',getdate());Select * from .

zodra we de bovenstaande code uitvoeren, kunnen we het resultaat van de tabelgegevens zien zoals weergegeven in onderstaande afbeelding.

aangezien we een nieuw record invoegen in de Department_Type3 tabel, kunnen we zien dat de kolom GroupName_old een NULL waarde heeft en GroupName_Current heeft de waarde van de recente insert. Laten we aannemen dat er verandering was in GroupName_current voor de “Engineering” Afdelingsnaam en we moeten de Department_Type3 tabel bijwerken met de nieuwe groupname_current waarde. Aangezien dit een Type3 dimensietabel is, is de vereiste hier om de kolom GroupName_old bij te werken met “Research and Development” en GroupName_Current met de nieuwe waarde.

--capturing Updated values using table variable.DECLARE @Updated table( int, varchar(50), varchar(50), varchar(50), datetime, datetime);Update Set ='IT', EffectiveDate=Getdate()OUTPUT deleted.DepartmentID, deleted.Name, deleted. as ,inserted.GroupName_current as GroupName_new, deleted.EffectiveDate as ModifiedDate_old,inserted.EffectiveDate as ModifiedDate_newINTO @UpdatedWhere ='Engineering'--Update the GroupName_old with old valuesUpdate aSet a.GroupName_old=b.GroupName_old, a.EffectiveDate=b.ModifiedDate_newfrom as ainner join @Updated as bon a.DepartmentID=b.DepartmentID--Querying the final tableSelect * from 

zodra we de bovenstaande code uitvoeren, zullen we de resultaten van de verwijderde interne tabel vastleggen in de @Updated table variabele en vervolgens die gegevens gebruiken om de definitieve tabelkolommen bij te werken voor het implementeren van deze langzaam veranderende dimensie van type 3. We kunnen het resultaat van de finaletabel gegevens zien voor en na de uitvoering van de bovenstaande verklaring zoals weergegeven in de onderstaande afbeeldingen.

Voordat de UPDATE-instructie-uitvoering

Na de UPDATE-instructie-uitvoering

OUTPUT-Component Beperkingen

een Aantal van de scenario ‘ s waar de OUTPUT-component wordt niet ondersteund:

1. DML-instructies verwijzen naar lokale gepartitioneerd uitzicht, verdeeld gepartitioneerd uitzicht, of externe tabellen.
2. statements invoegen die een uitvoer statement bevatten.
3. full-text predicaten zijn niet toegestaan in de outputclausule wanneer het databasecompatibiliteitsniveau is ingesteld op 100.
4. de OUTPUT in clausule kan niet worden gebruikt om in te voegen in een weergave, of rijset functie.
5. een door de gebruiker gedefinieerde functie kan niet worden aangemaakt als het een OUTPUT INTO-clausule bevat die een tabel als doel heeft

de volledige lijst wordt weergegeven in deze link: https://technet.microsoft.com/en-us/library/ms177564(v=sql.110).aspx

samenvatting

zoals getoond in de bovenstaande voorbeelden kunnen we zien dat de output clausule eenvoudig te gebruiken is en veel aangepaste codering kan vermijden om de output resultaten van bijgewerkte waarden met zowel oude als nieuwe waarden vast te leggen in een tabel variabele tijdens het uitvoeren van de query.