OUTPUT-component (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric Preview

Retourneert informatie uit of expressies op basis van elke rij die wordt beïnvloed door een INSERT, UPDATEof DELETEMERGE instructie. Deze resultaten kunnen worden geretourneerd naar de verwerkingstoepassing voor gebruik in bijvoorbeeld bevestigingsberichten, archivering en andere toepassingsvereisten. De resultaten kunnen ook worden ingevoegd in een tabel- of tabelvariabele. Daarnaast kunt u de resultaten van een OUTPUT component vastleggen in een geneste INSERT, UPDATE, DELETEof MERGE instructie en deze resultaten invoegen in een doeltabel of -weergave.

Used in:

• DELETE
• INSERT
• UPDATE
• MERGE

Transact-SQL syntaxis-conventies

Syntax

<OUTPUT_CLAUSE> ::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
    [ OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_expression } [ [ AS ] column_alias_identifier ]
    [ , ...n ]

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

Arguments

@table_variable

Hiermee geeft u een tabelvariabele op waarin de geretourneerde rijen worden ingevoegd in plaats van terug te keren naar de aanroeper. @table_variable moet worden gedeclareerd vóór de , UPDATEof DELETEMERGE de INSERTinstructie.

Als column_list niet is opgegeven, moet de tabelvariabele hetzelfde aantal kolommen hebben als de OUTPUT resultatenset. De uitzonderingen zijn identiteits- en berekende kolommen, die moeten worden overgeslagen. Als column_list is opgegeven, moeten weggelaten kolommen null-waarden toestaan of standaardwaarden aan deze kolommen toewijzen.

Zie de tabel voor meer informatie over tabelvariabelen.

output_table

Hiermee geeft u een tabel op waarin de geretourneerde rijen worden ingevoegd in plaats van terug te keren naar de aanroeper. output_table kan een tijdelijke tabel zijn.

Als column_list niet is opgegeven, moet de tabel hetzelfde aantal kolommen hebben als de OUTPUT resultatenset. De uitzonderingen zijn identiteits- en berekende kolommen, die moeten worden overgeslagen. Als column_list is opgegeven, moeten weggelaten kolommen null-waarden toestaan of standaardwaarden aan deze kolommen toewijzen.

output_table can't:

• Er zijn triggers ingeschakeld die erop zijn gedefinieerd.
• Deelnemen aan beide zijden van een FOREIGN KEY beperking.
• Beperkingen of ingeschakelde regels hebben CHECK .

column_list

Een optionele lijst met kolomnamen in de doeltabel van de INTO component. Dit is vergelijkbaar met de kolomlijst die is toegestaan in de instructie INSERT .

scalar_expression

Elke combinatie van symbolen en operatoren die één waarde oplevert. Statistische functies zijn niet toegestaan in scalar_expression.

Verwijzingen naar kolommen in de tabel die worden gewijzigd, moeten worden gekwalificeerd met het INSERTED of DELETED voorvoegsel.

column_alias_identifier

Een alternatieve naam die wordt gebruikt om te verwijzen naar de kolomnaam.

DELETED

Een kolomvoorvoegsel dat de waarde aangeeft die is verwijderd door de update- of verwijderbewerking en eventuele bestaande waarden die niet met de huidige bewerking worden gewijzigd. Kolommen die voorafgaan aan DELETED de waarde voordat de UPDATE, DELETEof MERGE instructie is voltooid.

DELETED kan niet worden gebruikt met de OUTPUT component in de INSERT instructie.

INSERTED

Een kolomvoorvoegsel dat de waarde aangeeft die is toegevoegd door de invoeg- of bijwerkbewerking en eventuele bestaande waarden die niet worden gewijzigd met de huidige bewerking. Kolommen voorafgegaan door INSERTED de waarde na de UPDATE, INSERTof MERGE instructie is voltooid, maar voordat triggers worden uitgevoerd.

INSERTED kan niet worden gebruikt met de OUTPUT component in de DELETE instructie.

from_table_name

Een kolomvoorvoegsel dat een tabel aangeeft die is opgenomen in de FROM component van een DELETE, UPDATEof MERGE instructie die wordt gebruikt om de rijen op te geven die moeten worden bijgewerkt of verwijderd.

Als de tabel die wordt gewijzigd ook wordt opgegeven in de FROM component, moet een verwijzing naar kolommen in die tabel worden gekwalificeerd met het INSERTED of DELETED voorvoegsel.

*

Het sterretje (*) geeft aan dat alle kolommen die worden beïnvloed door de verwijder-, invoeg- of bijwerkactie, worden geretourneerd in de volgorde waarin ze in de tabel voorkomen.

In de volgende DELETE instructie worden bijvoorbeeld OUTPUT DELETED.* alle kolommen geretourneerd die uit de ShoppingCartItem tabel zijn verwijderd:

DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.*;

column_name

Een expliciete kolomreferentie. Een verwijzing naar de tabel die wordt gewijzigd, moet naar behoren worden gekwalificeerd door het INSERTED voorvoegsel of het DELETED voorvoegsel, bijvoorbeeld: INSERTED.<column_name>.

$action

Alleen beschikbaar voor de MERGE instructie. Hiermee geeft u een kolom van het type nvarchar(10) in de OUTPUT component op in een instructie die een MERGE van de drie waarden retourneert voor elke rij: INSERT, UPDATEof DELETE, volgens de actie die op die rij is uitgevoerd.

Remarks

De OUTPUT <dml_select_list> component en de OUTPUT <dml_select_list> INTO { @table_variable | output_table } component kunnen worden gedefinieerd in één INSERT, UPDATEof DELETEMERGE instructie.

De OUTPUT component kan handig zijn om de waarde van identiteit of berekende kolommen na een INSERT of UPDATE meer bewerkingen op te halen.

Wanneer een berekende kolom wordt opgenomen in de <dml_select_list>kolom, is de bijbehorende kolom in de uitvoertabel of tabelvariabele geen berekende kolom. De waarden in de nieuwe kolom zijn de waarden die zijn berekend op het moment dat de instructie werd uitgevoerd.

De volgorde waarin de wijzigingen worden toegepast op de tabel en de volgorde waarin de rijen worden ingevoegd in de uitvoertabel of tabelvariabele, komen niet gegarandeerd overeen.

Als parameters of variabelen worden gewijzigd als onderdeel van een UPDATE instructie, retourneert de OUTPUT component altijd de waarde van de parameter of variabele zoals deze was voordat de instructie werd uitgevoerd in plaats van de gewijzigde waarde.

U kunt deze gebruiken OUTPUT met een UPDATE of DELETE een instructie die wordt weergegeven op een cursor die gebruikmaakt van WHERE CURRENT OF de syntaxis.

De OUTPUT component wordt niet ondersteund in de volgende instructies:

• DML-instructies die verwijzen naar lokale gepartitioneerde weergaven, gedistribueerde gepartitioneerde weergaven of externe tabellen.

• INSERT instructies die een EXECUTE instructie bevatten.

• Predicaten voor volledige tekst zijn niet toegestaan in de OUTPUT component wanneer het compatibiliteitsniveau van de database is ingesteld op 100.

• De OUTPUT INTO component kan niet worden gebruikt om in een weergave of een rijsetfunctie in te voegen.

• Een door de gebruiker gedefinieerde functie kan niet worden gemaakt als deze een OUTPUT INTO component bevat met een tabel als doel.

Om niet-deterministisch gedrag te voorkomen, kan de OUTPUT component niet de volgende verwijzingen bevatten:

• Subquery's of door de gebruiker gedefinieerde functies die toegang tot gebruikers- of systeemgegevens uitvoeren, of worden ervan uitgegaan dat deze toegang wordt uitgevoerd. Door de gebruiker gedefinieerde functies worden verondersteld om gegevenstoegang uit te voeren als ze geen schemagebonden zijn.

• Een kolom van een weergave- of inline-tabelwaardefunctie wanneer die kolom wordt gedefinieerd met een van de volgende methoden:

  • A subquery.

  • Een door de gebruiker gedefinieerde functie die toegang tot gebruikers- of systeemgegevens uitvoert, of wordt ervan uitgegaan dat deze toegang wordt uitgevoerd.

  • Een berekende kolom die een door de gebruiker gedefinieerde functie bevat die toegang tot gebruikers- of systeemgegevens uitvoert in de definitie.

  Wanneer SQL Server een dergelijke kolom in de OUTPUT component detecteert, wordt fout 4186 gegenereerd.

Gegevens invoegen die worden geretourneerd vanuit een OUTPUT-component in een tabel

Wanneer u de resultaten van een OUTPUT component vastlegt in een geneste INSERT, UPDATEof instructie DELETEMERGE en deze resultaten invoegt in een doeltabel, moet u rekening houden met de volgende informatie:

• De hele bewerking is atomisch. Zowel de INSERT instructie als de geneste DML-instructie die de OUTPUT component uitvoeren bevat, of de hele instructie mislukt.

• De volgende beperkingen zijn van toepassing op het doel van de outer-instructie INSERT :

  • Het doel kan geen externe tabel, weergave of algemene tabelexpressie zijn.

  • Het doel kan geen beperking hebben FOREIGN KEY of waarnaar kan worden verwezen door een FOREIGN KEY beperking.

  • Triggers kunnen niet worden gedefinieerd voor het doel.

  • Het doel kan niet deelnemen aan samenvoegreplicatie of updatable abonnementen voor transactionele replicatie.

• De volgende beperkingen zijn van toepassing op de geneste DML-instructie:

  • Het doel kan geen externe tabel of gepartitioneerde weergave zijn.

  • De bron zelf kan geen component bevatten <dml_table_source> .

• De OUTPUT INTO component wordt niet ondersteund in INSERT instructies die een <dml_table_source> component bevatten.

• @@ROWCOUNT retourneert de rijen die alleen door de buitenste INSERT instructie zijn ingevoegd.

• @@IDENTITY, SCOPE_IDENTITYen IDENT_CURRENT retourneer identiteitswaarden die alleen worden gegenereerd door de geneste DML-instructie en niet waarden die door de buitenste INSERT instructie worden gegenereerd.

• Querymeldingen behandelen de instructie als één entiteit en het type bericht dat wordt gemaakt, is het type geneste DML, zelfs als de belangrijke wijziging afkomstig is van de buitenste INSERT instructie zelf.

• In de <dml_table_source> component kunnen de SELECT en WHERE componenten geen subquery's, statistische functies, classificatiefuncties, predicaten in volledige tekst, door de gebruiker gedefinieerde functies die gegevenstoegang uitvoeren of de TEXTPTR() functie bevatten.

Parallelism

Een OUTPUT component die resultaten naar de client of tabelvariabele retourneert, maakt altijd gebruik van een serieel plan.

In de context van een database die is ingesteld op compatibiliteitsniveau 130 of hoger, komt INSERT...SELECTWITH (TABLOCK)SELECT de doeltabel voor de INSERT...SELECT database in aanmerking voor parallellisme, afhankelijk van de kosten van de substructuur.OUTPUT...INTO De doeltabel waarnaar in de OUTPUT INTO component wordt verwezen, komt niet in aanmerking voor parallelle uitvoering.

Triggers

Kolommen die worden geretourneerd uit OUTPUT de gegevens worden weergegeven nadat de , UPDATEof DELETE instructie INSERTis voltooid, maar voordat triggers worden uitgevoerd.

Voor INSTEAD OF triggers worden de geretourneerde resultaten gegenereerd alsof de INSERT, UPDATEof DELETE daadwerkelijk heeft plaatsgevonden, zelfs als er geen wijzigingen plaatsvinden als gevolg van de triggerbewerking. Als een instructie met een OUTPUT component wordt gebruikt in de hoofdtekst van een trigger, moeten tabelaliassen worden gebruikt om te verwijzen naar de trigger ingevoegde en verwijderde tabellen om te voorkomen dat kolomverwijzingen worden gedupliceerd met de INSERTED en DELETED tabellen die zijn gekoppeld aan OUTPUT.

Als de OUTPUT component is opgegeven zonder ook het INTO trefwoord op te geven, kan het doel van de DML-bewerking geen ingeschakelde trigger hebben gedefinieerd voor de opgegeven DML-actie. Als de OUTPUT component bijvoorbeeld is gedefinieerd in een UPDATE instructie, kan de doeltabel geen ingeschakelde UPDATE triggers hebben.

Als de sp_configure optie geen resultaten van triggers toekent, zorgt een OUTPUT component zonder een INTO component ervoor dat de instructie mislukt wanneer deze vanuit een trigger wordt aangeroepen.

Data types

De OUTPUT component ondersteunt de gegevenstypen van grote objecten: nvarchar(max), varchar(max), varbinary(max), tekst, ntext, afbeelding en XML. Wanneer u de .WRITE component in de UPDATE instructie gebruikt om een kolom nvarchar(max), varchar(max), of varbinary(max) te wijzigen, worden de volledige voor- en naafbeeldingen van de waarden geretourneerd als ernaar wordt verwezen. De TEXTPTR() functie kan niet worden weergegeven als onderdeel van een expressie in een tekst-, ntekst- of afbeeldingskolom in de OUTPUT component.

Queues

U kunt in OUTPUT toepassingen die tabellen gebruiken als wachtrijen of om tussenliggende resultatensets te bewaren. Dat wil gezegd, de toepassing voegt voortdurend rijen toe aan of verwijdert deze uit de tabel. In het volgende voorbeeld wordt de OUTPUT component in een DELETE instructie gebruikt om de verwijderde rij terug te keren naar de aanroepende toepassing.

USE AdventureWorks2022;
GO

DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT DELETED.*
WHERE DatabaseLogID = 7;
GO

In dit voorbeeld wordt een rij verwijderd uit een tabel die wordt gebruikt als wachtrij en worden de verwijderde waarden in één actie geretourneerd naar de verwerkingstoepassing. Andere semantiek kan ook worden geïmplementeerd, zoals het gebruik van een tabel om een stack te implementeren. SQL Server garandeert echter niet de volgorde waarin rijen worden verwerkt en geretourneerd door DML-instructies met behulp van de OUTPUT component. Het is aan de toepassing om een geschikte WHERE component op te nemen die de gewenste semantiek kan garanderen of dat er geen gegarandeerde volgorde is wanneer meerdere rijen in aanmerking komen voor de DML-bewerking. In het volgende voorbeeld wordt een subquery gebruikt en ervan uitgegaan dat de uniekheid een kenmerk van de DatabaseLogID kolom is om de gewenste volgordesemantiek te implementeren.

USE tempdb;
GO

CREATE TABLE dbo.table1
(
    id INT,
    employee VARCHAR(32)
);
GO

INSERT INTO dbo.table1
VALUES (1, 'Fred'),
    (2, 'Tom'),
    (3, 'Sally'),
    (4, 'Alice');
GO

DECLARE @MyTableVar TABLE (
    id INT,
    employee VARCHAR(32)
);

PRINT 'table1, before delete';

SELECT *
FROM dbo.table1;

DELETE
FROM dbo.table1
OUTPUT DELETED.*
INTO @MyTableVar
WHERE id = 4
    OR id = 2;

PRINT 'table1, after delete';

SELECT *
FROM dbo.table1;

PRINT '@MyTableVar, after delete';

SELECT *
FROM @MyTableVar;

DROP TABLE dbo.table1;

Dit zijn de resultaten:

table1, before delete
id          employee
----------- ------------------------------
1           Fred
2           Tom
3           Sally
4           Alice

table1, after delete
id          employee
----------- ------------------------------
1           Fred
3           Sally

@MyTableVar, after delete
id          employee
----------- ------------------------------
2           Tom
4           Alice

Permissions

SELECT machtigingen zijn vereist voor kolommen die zijn opgehaald via <dml_select_list> of worden gebruikt in <scalar_expression>.

INSERT machtigingen zijn vereist voor alle tabellen die zijn opgegeven in <output_table>.

Examples

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase die u kunt downloaden van de startpagina van Microsoft SQL Server Samples en Community Projects .

A. OUTPUT INTO gebruiken met een INSERT-instructie

In het volgende voorbeeld wordt een rij in de ScrapReason tabel ingevoegd en wordt de OUTPUT component gebruikt om de resultaten van de instructie te retourneren aan de @MyTableVar tabelvariabele. Omdat de ScrapReasonID kolom is gedefinieerd met een id-eigenschap, wordt er geen waarde opgegeven in de INSERT instructie voor die kolom. De waarde die door de database-engine voor die kolom wordt gegenereerd, wordt echter geretourneerd in de OUTPUT component in de kolom INSERTED.ScrapReasonID.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    NewScrapReasonID SMALLINT,
    Name VARCHAR(50),
    ModifiedDate DATETIME
);

INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO

B. UITVOER gebruiken met een DELETE-instructie

In het volgende voorbeeld worden alle rijen in de ShoppingCartItem tabel verwijderd. De component OUTPUT DELETED.* geeft aan dat de resultaten van de DELETE instructie, dat wil zeggen, alle kolommen in de verwijderde rijen, worden geretourneerd naar de aanroepende toepassing. De SELECT instructie die volgt controleert de resultaten van de verwijderbewerking in de ShoppingCartItem tabel.

USE AdventureWorks2022;
GO

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*
WHERE ShoppingCartID = 20621;

--Verify the rows in the table matching the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO

C. OUTPUT INTO gebruiken met een UPDATE-instructie

In het volgende voorbeeld wordt de VacationHours kolom in de Employee tabel bijgewerkt met 25 procent voor de eerste 10 rijen. De OUTPUT component retourneert de VacationHours waarde die bestaat voordat de UPDATE instructie in de kolom DELETED.VacationHourswordt toegepast en de bijgewerkte waarde in de kolom INSERTED.VacationHours op de @MyTableVar tabelvariabele.

Er volgen twee SELECT instructies die de waarden retourneren in @MyTableVar en de resultaten van de updatebewerking in de Employee tabel.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    ModifiedDate DATETIME);

UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE()
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

D. UITVOER INTO gebruiken om een expressie te retourneren

Het volgende voorbeeld bouwt voort op voorbeeld C door een expressie in de OUTPUT component te definiëren als het verschil tussen de bijgewerkte VacationHours waarde en de VacationHours waarde voordat de update werd toegepast. De waarde van deze expressie wordt geretourneerd naar de @MyTableVar tabelvariabele in de kolom VacationHoursDifference.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    EmpID INT NOT NULL,
    OldVacationHours INT,
    NewVacationHours INT,
    VacationHoursDifference INT,
    ModifiedDate DATETIME);

UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE()
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.VacationHours - DELETED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours,
    VacationHoursDifference, ModifiedDate
FROM @MyTableVar;
GO
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

E. OUTPUT INTO gebruiken met from_table_name in een UPDATE-instructie

In het volgende voorbeeld wordt de ScrapReasonID kolom in de WorkOrder tabel bijgewerkt voor alle werkorders met een opgegeven ProductID en ScrapReasonID. De OUTPUT INTO component retourneert waarden uit de tabel die wordt bijgewerkt (WorkOrder) en ook uit de Product tabel. De Product tabel wordt gebruikt in de FROM component om de rijen op te geven die moeten worden bijgewerkt. Omdat er een AFTER UPDATE trigger voor de WorkOrder tabel is gedefinieerd, is het INTO trefwoord vereist.

USE AdventureWorks2022;
GO

DECLARE @MyTestVar TABLE (
    OldScrapReasonID INT NOT NULL,
    NewScrapReasonID INT NOT NULL,
    WorkOrderID INT NOT NULL,
    ProductID INT NOT NULL,
    ProductName NVARCHAR(50)NOT NULL);

UPDATE Production.WorkOrder
SET ScrapReasonID = 4
OUTPUT DELETED.ScrapReasonID,
       INSERTED.ScrapReasonID,
       INSERTED.WorkOrderID,
       INSERTED.ProductID,
       p.Name
    INTO @MyTestVar
FROM Production.WorkOrder AS wo
    INNER JOIN Production.Product AS p
    ON wo.ProductID = p.ProductID
    AND wo.ScrapReasonID= 16
    AND p.ProductID = 733;

SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,
    ProductID, ProductName
FROM @MyTestVar;
GO

F. OUTPUT INTO gebruiken met from_table_name in een DELETE-instructie

In het volgende voorbeeld worden rijen in de ProductProductPhoto tabel verwijderd op basis van zoekcriteria die zijn gedefinieerd in de FROM instructiecomponent DELETE . De OUTPUT component retourneert kolommen uit de tabel die wordt verwijderd (DELETED.ProductID, DELETED.ProductPhotoID) en kolommen uit de Product tabel. Deze tabel wordt gebruikt in de FROM component om de rijen op te geven die moeten worden verwijderd.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    ProductID INT NOT NULL,
    ProductName NVARCHAR(50)NOT NULL,
    ProductModelID INT NOT NULL,
    PhotoID INT NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
    ON ph.ProductID = p.ProductID
    WHERE p.ProductModelID BETWEEN 120 and 130;

--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID
FROM @MyTableVar
ORDER BY ProductModelID;
GO

G. OUTPUT INTO gebruiken met een gegevenstype groot object

In het volgende voorbeeld wordt een gedeeltelijke waarde in DocumentSummaryde kolom nvarchar(max) in de Production.Document tabel bijgewerkt met behulp van de .WRITE component. Het woord wordt vervangen door het woord componentsfeatures door het vervangende woord op te geven, de beginlocatie (verschuiving) van het woord dat moet worden vervangen in de bestaande gegevens en het aantal tekens dat moet worden vervangen (lengte). In het voorbeeld wordt de OUTPUT component gebruikt om de voor- en naafbeeldingen van de DocumentSummary kolom te retourneren aan de @MyTableVar tabelvariabele. De volledige voor- en naafbeeldingen van de DocumentSummary kolom worden geretourneerd.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    SummaryBefore NVARCHAR(MAX),
    SummaryAfter NVARCHAR(MAX)
);

UPDATE Production.Document
SET DocumentSummary.WRITE(N'features', 28, 10)
OUTPUT DELETED.DocumentSummary,
       INSERTED.DocumentSummary
    INTO @MyTableVar
WHERE Title = N'Front Reflector Bracket Installation';

SELECT SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO

H. UITVOER gebruiken in een IN PLAATS VAN trigger

In het volgende voorbeeld wordt de OUTPUT component in een trigger gebruikt om de resultaten van de triggerbewerking te retourneren. Eerst wordt een weergave gemaakt in de ScrapReason tabel en vervolgens wordt een INSTEAD OF INSERT trigger gedefinieerd in de weergave waarmee alleen de Name kolom van de basistabel kan worden gewijzigd door de gebruiker. Omdat de kolom een IDENTITY kolom ScrapReasonID in de basistabel is, negeert de trigger de door de gebruiker opgegeven waarde. Hierdoor kan de database-engine automatisch de juiste waarde genereren. De waarde die door de gebruiker ModifiedDate wordt opgegeven, wordt ook genegeerd en is ingesteld op de huidige datum. De OUTPUT component retourneert de waarden die daadwerkelijk in de ScrapReason tabel zijn ingevoegd.

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.vw_ScrapReason', 'V') IS NOT NULL
    DROP VIEW dbo.vw_ScrapReason;
GO

CREATE VIEW dbo.vw_ScrapReason
AS
SELECT ScrapReasonID,
    Name,
    ModifiedDate
FROM Production.ScrapReason;
GO

CREATE TRIGGER dbo.io_ScrapReason ON dbo.vw_ScrapReason
INSTEAD OF INSERT
AS
BEGIN
    --ScrapReasonID is not specified in the list of columns to be inserted
    --because it is an IDENTITY column.
    INSERT INTO Production.ScrapReason (
        Name,
        ModifiedDate
    )
    OUTPUT INSERTED.ScrapReasonID,
        INSERTED.Name,
        INSERTED.ModifiedDate
    SELECT Name, GETDATE()
    FROM INSERTED;
END
GO

INSERT vw_ScrapReason (
    ScrapReasonID,
    Name,
    ModifiedDate
)
VALUES (
    99,
    N'My scrap reason',
    '20030404'
);
GO

Dit is de resultatenset die is gegenereerd op 12 april 2004 ('2004-04-12'). De ScrapReasonIDActual en ModifiedDate kolommen weerspiegelen de waarden die zijn gegenereerd door de triggerbewerking in plaats van de waarden die zijn opgegeven in de INSERT instructie.

ScrapReasonID  Name             ModifiedDate
-------------  ---------------- -----------------------
17             My scrap reason  2004-04-12 16:23:33.050

I. OUTPUT INTO gebruiken met identiteits- en berekende kolommen

In het volgende voorbeeld wordt de EmployeeSales tabel gemaakt en vervolgens meerdere rijen erin ingevoegd met behulp van een INSERT instructie met een SELECT instructie om gegevens op te halen uit brontabellen. De EmployeeSales tabel bevat een identiteitskolom (EmployeeID) en een berekende kolom (ProjectedSales).

USE AdventureWorks2022;
GO

IF OBJECT_ID('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO

CREATE TABLE dbo.EmployeeSales (
    EmployeeID INT IDENTITY(1, 5) NOT NULL,
    LastName NVARCHAR(20) NOT NULL,
    FirstName NVARCHAR(20) NOT NULL,
    CurrentSales MONEY NOT NULL,
    ProjectedSales AS CurrentSales * 1.10
);
GO

DECLARE @MyTableVar TABLE (
    EmployeeID INT NOT NULL,
    LastName NVARCHAR(20) NOT NULL,
    FirstName NVARCHAR(20) NOT NULL,
    CurrentSales MONEY NOT NULL,
    ProjectedSales MONEY NOT NULL
);

INSERT INTO dbo.EmployeeSales (
    LastName,
    FirstName,
    CurrentSales
)
OUTPUT INSERTED.EmployeeID,
    INSERTED.LastName,
    INSERTED.FirstName,
    INSERTED.CurrentSales,
    INSERTED.ProjectedSales
INTO @MyTableVar
SELECT c.LastName,
    c.FirstName,
    sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
    ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY c.LastName,
    c.FirstName;

SELECT EmployeeID,
    LastName,
    FirstName,
    CurrentSales,
    ProjectedSales
FROM @MyTableVar;
GO

SELECT EmployeeID,
    LastName,
    FirstName,
    CurrentSales,
    ProjectedSales
FROM dbo.EmployeeSales;
GO

J. OUTPUT en OUTPUT INTO in één instructie gebruiken

In het volgende voorbeeld worden rijen in de ProductProductPhoto tabel verwijderd op basis van zoekcriteria die zijn gedefinieerd in de FROM instructiecomponent DELETE . De OUTPUT INTO component retourneert kolommen uit de tabel die wordt verwijderd (DELETED.ProductID, DELETED.ProductPhotoID) en kolommen uit de Product tabel naar de @MyTableVar tabelvariabele. De Product tabel wordt gebruikt in de FROM component om de rijen op te geven die moeten worden verwijderd. De OUTPUT component retourneert de DELETED.ProductIDkolommen DELETED.ProductPhotoID en de datum en tijd waarop de rij uit de ProductProductPhoto tabel is verwijderd naar de aanroepende toepassing.

USE AdventureWorks2022;
GO

DECLARE @MyTableVar TABLE (
    ProductID INT NOT NULL,
    ProductName NVARCHAR(50) NOT NULL,
    ProductModelID INT NOT NULL,
    PhotoID INT NOT NULL
);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
    p.Name,
    p.ProductModelID,
    DELETED.ProductPhotoID
INTO @MyTableVar
OUTPUT DELETED.ProductID,
    DELETED.ProductPhotoID,
    GETDATE() AS DeletedDate
FROM Production.ProductProductPhoto AS ph
INNER JOIN Production.Product AS p
    ON ph.ProductID = p.ProductID
WHERE p.ProductID BETWEEN 800
        AND 810;

--Display the results of the table variable.
SELECT ProductID,
    ProductName,
    PhotoID,
    ProductModelID
FROM @MyTableVar;
GO

K. Gegevens invoegen die zijn geretourneerd met een OUTPUT-component

In het volgende voorbeeld worden gegevens vastgelegd die worden geretourneerd uit de OUTPUT component van een MERGE instructie en worden die gegevens in een andere tabel ingevoegd. De MERGE instructie werkt de Quantity kolom van de ProductInventory tabel dagelijks bij op basis van orders die in de SalesOrderDetail tabel worden verwerkt. Ook worden rijen verwijderd voor producten waarvan de voorraden worden verwijderd tot 0 of minder. In het voorbeeld worden de rijen vastgelegd die worden verwijderd en ingevoegd in een andere tabel, ZeroInventorywaarmee producten worden bijgehouden zonder inventaris.

USE AdventureWorks2022;
GO

IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO

--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (
    DeletedProductID INT,
    RemovedOnDate DATETIME
    );
GO

INSERT INTO Production.ZeroInventory (
    DeletedProductID,
    RemovedOnDate
)
SELECT ProductID,
    GETDATE()
FROM (
    MERGE Production.ProductInventory AS pi
    USING (
        SELECT ProductID,
            SUM(OrderQty)
        FROM Sales.SalesOrderDetail AS sod
        INNER JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
                AND soh.OrderDate = '20070401'
        GROUP BY ProductID
        ) AS src(ProductID, OrderQty)
        ON (pi.ProductID = src.ProductID)
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty <= 0
        THEN
            DELETE
    WHEN MATCHED
        THEN
            UPDATE
            SET pi.Quantity = pi.Quantity - src.OrderQty
    OUTPUT $ACTION,
        DELETED.ProductID
    ) AS Changes(Action, ProductID)
WHERE Action = 'DELETE';

IF @@ROWCOUNT = 0
    PRINT 'Warning: No rows were inserted';
GO

SELECT DeletedProductID,
    RemovedOnDate
FROM Production.ZeroInventory;
GO

Related content

• DELETE (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• table (Transact-SQL)
• CREATE TRIGGER (Transact-SQL)
• sp_configure (Transact-SQL)