Clausola OUTPUT (Transact-SQL)

Quando si acquisiscono i risultati di una clausola OUTPUT in un'istruzione nidificata INSERT, UPDATE, DELETE o MERGE e si inseriscono tali risultati in una tabella di destinazione, tenere presente le considerazioni seguenti:

• L'intera operazione è di tipo atomico. È necessario eseguire sia l'istruzione INSERT esterna che l'istruzione DMl interna contenente la clausola OUTPUT. In caso contrario, l'intera istruzione avrà esito negativo.

• Alla destinazione dell'istruzione INSERT esterna si applicano le restrizioni seguenti:

  • La destinazione non può essere una tabella remota, una vista o un'espressione di tabella comune.

  • Nella destinazione non può essere presente un vincolo FOREIGN KEY né un vincolo FOREIGN KEY può fare riferimento alla destinazione stessa.

  • Nella destinazione non è possibile definire trigger.

  • La destinazione non può partecipare alla replica di tipo merge oppure a sottoscrizioni aggiornabili per la replica transazionale.

• All'istruzione DML nidificata si applicano le restrizioni seguenti:

  • La destinazione non può essere una tabella remota o una vista partizionata.

  • Nell'origine non può essere contenuta una clausola <dml_table_source>.

• La clausola OUTPUT INTO non è supportata in istruzioni INSERT che contengono una clausola <dml_table_source>.

• @@ROWCOUNT restituisce solo le righe inserite dall'istruzione INSERT esterna.

• @@IDENTITY, SCOPE_IDENTITY e IDENT_CURRENT restituiscono solo i valori Identity generati dall'istruzione DML nidificata e non quelli generati dall'istruzione INSERT esterna.

• Le notifiche delle query considerano l'istruzione come entità singola e il tipo di qualsiasi messaggio creato sarà il tipo dell'istruzione DML nidificata, anche se la modifica significativa proviene dall'istruzione INSERT esterna stessa.

• Nella clausola <dml_table_source> le clausole SELECT e WHERE non possono contenere sottoquery, funzioni di aggregazione, funzioni di rango, predicati full-text, funzioni definite dall'utente che eseguono l'accesso ai dati o la funzione TEXTPTR.

Le colonne restituite da OUTPUT riflettono i dati dopo il completamento dell'istruzione INSERT, UPDATE o DELETE ma prima dell'esecuzione dei trigger.

Per i trigger INSTEAD OF, i risultati restituiti vengono generati come se le istruzioni INSERT, UPDATE o DELETE siano state effettivamente completate, anche se non si verifica alcuna modifica come risultato dell'operazione trigger. Se un'istruzione che include una clausola OUTPUT viene utilizzata all'interno del corpo di un trigger, gli alias di tabella devono essere utilizzati per fare riferimento al trigger inserted e alle tabelle deleted per evitare la duplicazione dei riferimenti di colonna con le tabelle INSERTED e DELETED associate a OUTPUT.

Se la clausola OUTPUT viene specificata senza specificare anche la parola chiave INTO, la destinazione dell'operazione DML non può avere trigger abilitati definiti per l'azione DML specificata. Ad esempio, se la clausola OUTPUT viene definita in un'istruzione UPDATE, la tabella di destinazione non può avere alcun trigger UPDATE abilitato.

Se è impostata l'opzione disallow results from triggers di sp_configure, una clausola OUTPUT senza clausola INTO provoca l'esito negativo dell'istruzione quando viene richiamata dall'interno di un trigger.

La clausola OUTPUT supporta i tipi di dati LOB nvarchar(max), varchar(max), varbinary(max), text, ntext, image e xml. Quando si utilizza la clausola .WRITE nell'istruzione UPDATE per modificare una colonna di tipo nvarchar(max), varchar(max) o varbinary(max), vengono restituite le immagini complete precedenti e successive dei valori se contengono dei riferimenti. La funzione TEXTPTR( ) non può comparire in un'espressione in una colonna di tipo text, ntext o image nella clausola OUTPUT.

È possibile utilizzare la clausola OUTPUT nelle applicazioni che utilizzano le tabelle come code oppure per mantenere i risultati intermedi delle query. In altre parole, l'applicazione aggiunge o rimuove costantemente le righe dalla tabella. Nell'esempio seguente viene utilizzata la clausola OUTPUT in un'istruzione DELETE per restituire la riga eliminata all'applicazione chiamante.

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

In questo esempio viene rimossa una riga da una tabella utilizzata come coda e i valori eliminati vengono restituiti all'applicazione di elaborazione in una singola azione. È possibile implementare anche altri tipi di semantica, ad esempio l'utilizzo di una tabella per implementare uno stack. SQL Server non garantisce tuttavia l'ordine in cui le righe vengono elaborate e restituite dalle istruzioni DML che utilizzano la clausola OUTPUT. Spetta all'applicazione includere una clausola WHERE appropriata per garantire la semantica desiderata oppure tenere in considerazione che quando più righe possono essere incluse nell'operazione DML, non viene garantito alcun ordine particolare. Nell'esempio seguente viene utilizzata una sottoquery e viene presupposto che l'unicità sia una caratteristica della colonna DatabaseLogID per implementare la semantica di ordinamento desiderata.

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 id, employee FROM dbo.table1;

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

PRINT 'table1, after delete';
SELECT id, employee FROM table1;

PRINT '@MyTableVar, after delete';
SELECT id, employee FROM @MyTableVar;

DROP TABLE dbo.table1;

--Results
--table1, before delete
--id          employee
------------- ------------------------------
--1           Fred
--2           Tom
--3           Sally
--4           Alice
--
--table1 results, after delete
--id          employee
------------- ------------------------------
--1           Fred
--3           Sally
--
--@MyTableVar results, after delete
--id          employee
------------- ------------------------------
--2           Tom
--4           Alice

Nell'esempio seguente viene inserita una riga nella tabella ScrapReason e viene utilizzata la clausola OUTPUT per restituire i risultati dell'istruzione alla variabile @MyTableVartable. Poiché la colonna ScrapReasonID è definita con una proprietà IDENTITY, non è specificato un valore nell'istruzione INSERT per quella colonna. Si noti tuttavia che il valore generato da Motore di database per quella colonna viene restituito nella clausola OUTPUT nella colonna INSERTED.ScrapReasonID.

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID 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 ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

Nell'esempio seguente vengono eliminate tutte le righe nella tabella ShoppingCartItem. La clausola OUTPUT DELETED.* specifica che i risultati dell'istruzione DELETE, ovvero tutte le colonne nelle righe eliminate, devono essere restituiti all'applicazione chiamante. L'istruzione SELECT che segue verifica i risultati dell'operazione di eliminazione nella tabella ShoppingCartItem.

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

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

Nell'esempio seguente viene aggiornata la colonna VacationHours nella tabella Employee del 25% per le prime 10 righe e viene inoltre impostato il valore nella colonna ModifiedDate sulla data corrente. La clausola OUTPUT restituisce il valore VacationHours che esiste prima dell'applicazione dell'istruzione UPDATE nella colonna deleted.VacationHours e il valore aggiornato nella colonna inserted.VacationHours alla variabile @MyTableVartable.

Seguono due istruzioni SELECT che restituiscono i valori in @MyTableVar e i risultati dell'operazione di aggiornamento nella tabella Employee.

USE AdventureWorks;
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.EmployeeID,
       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) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Nell'esempio seguente viene utilizzato come base l'esempio C definendo un'espressione nella clausola OUTPUT come differenza tra il valore VacationHours aggiornato e il valore VacationHours prima dell'applicazione dell'aggiornamento. Il valore di questa espressione viene restituito alla variabile @MyTableVartable nella colonna VacationHoursDifference.

USE AdventureWorks;
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.EmployeeID,
       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) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Nell'esempio seguente viene aggiornata la colonna ScrapReasonID nella tabella WorkOrder per tutti gli ordini di lavoro con un ProductID e ScrapReasonID specificato. La clausola OUTPUT INTO restituisce i valori dalla tabella in fase di aggiornamento (WorkOrder) e anche dalla tabella Product. La tabella Product viene utilizzata nella clausola FROM per specificare le righe da aggiornare. Poiché per la tabella WorkOrder è stato definito un trigger AFTER UPDATE, è necessaria la parola chiave INTO.

USE AdventureWorks;
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

Nell'esempio seguente vengono eliminate le righe nella tabella ProductProductPhoto in base ai criteri di ricerca definiti nella clausola FROM dell'istruzione DELETE. La clausola OUTPUT restituisce le colonne dalla tabella che viene eliminata (DELETED.ProductID, DELETED.ProductPhotoID) e dalla tabella Product. Questa tabella viene utilizzata nella clausola FROM per specificare le righe da eliminare.

USE AdventureWorks;
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

Nell'esempio seguente viene aggiornato un valore parziale in DocumentSummary, una colonna di tipo nvarchar(max) della tabella Production.Document, tramite la clausola .WRITE. La parola components viene sostituta con la parola features specificando la parola sostitutiva, la posizione iniziale (offset) della parola da sostituire nei dati esistenti e il numero di caratteri da sostituire (lunghezza). Nell'esempio viene utilizzata la clausola OUTPUT per restituire le immagini precedenti e successive della colonna DocumentSummary alla variabile @MyTableVartable. Si noti che vengono restituite le immagini precedenti e successive della colonna DocumentSummary.

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    DocumentID int NOT NULL,
    SummaryBefore nvarchar(max),
    SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT inserted.DocumentID,
       deleted.DocumentSummary, 
       inserted.DocumentSummary 
    INTO @MyTableVar
WHERE DocumentID = 3 ;
SELECT DocumentID, SummaryBefore, SummaryAfter 
FROM @MyTableVar;
GO

Nell'esempio seguente viene utilizzata la clausola OUTPUT in un trigger per restituire i risultati dell'operazione trigger. Viene prima creata una vista nella tabella ScrapReason e quindi definito un trigger INSTEAD OF INSERT nella vista che consente all'utente di modificare esclusivamente la colonna Name della tabella di base. Poiché la colonna ScrapReasonID è una colonna IDENTITY nella tabella di base, il trigger ignora il valore specificato dall'utente, consentendo a Motore di database di generare automaticamente il valore corretto. Inoltre, il valore specificato dall'utente per ModifiedDate viene ignorato e impostato sulla data corrente. La clausola OUTPUT restituisce i valori di fatto inseriti nella tabella ScrapReason.

USE AdventureWorks;
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

Di seguito è riportato il set di risultati generato il 12 aprile 2004 ('2004-04-12'). Si noti che le colonne ScrapReasonIDActual e ModifiedDate riflettono i valori generati dall'operazione trigger invece dei valori specificati nell'istruzione INSERT.

ScrapReasonID  Name             ModifiedDate

-------------  ---------------- -----------------------

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

Nell'esempio seguente viene creata la tabella EmployeeSales e quindi vengono inserite diverse righe nella tabella stessa tramite un'istruzione INSERT con un'istruzione SELECT per recuperare i dati dalle tabelle di origine. La tabella EmployeeSales include una colonna Identity (EmployeeID) e una colonna calcolata (ProjectedSales). Poiché questi valori vengono generati dal Motore di database di SQL Server durante l'operazione di inserimento, nessuna di queste colonne può essere definita in @MyTableVar.

USE AdventureWorks ;
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(
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL
  );

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

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

Nell'esempio seguente vengono eliminate le righe nella tabella ProductProductPhoto in base ai criteri di ricerca definiti nella clausola FROM dell'istruzione DELETE. La clausola OUTPUT INTO restituisce le colonne dalla tabella che viene eliminata (deleted.ProductID, deleted.ProductPhotoID) e dalla tabella Product alla variabile @MyTableVartable. La tabella Product viene utilizzata nella clausola FROM per specificare le righe da eliminare. La clausola OUTPUT restituisce all'applicazione chiamante le colonne deleted.ProductID, deleted.ProductPhotoID e la data e l'ora in cui la riga è stata eliminata dalla tabella ProductProductPhoto.

USE AdventureWorks;
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
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

Nell'esempio seguente vengono acquisiti i dati restituiti dalla clausola OUTPUT di un'istruzione MERGE e tali dati vengono inseriti in un'altra tabella. L'istruzione MERGE aggiorna la colonna Quantity della tabella ProductInventory su base giornaliera, in base agli ordini elaborati nella tabella SalesOrderDetail. Vengono inoltre eliminate le righe dei prodotti le cui scorte scendono a 0 oppure a un valore inferiore. In questo esempio vengono acquisite le righe eliminate, che vengono inserite in un'altra tabella, ZeroInventory, in cui viene tenuta traccia dei prodotti senza scorte.

USE AdventureWorks;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (ProductID int);
GO

INSERT INTO Production.ZeroInventory (ProductID)
SELECT ProductID
FROM
(   MERGE Production.ProductInventory AS pi
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
           JOIN Sales.SalesOrderHeader AS soh
           ON sod.SalesOrderID = soh.SalesOrderID
           AND soh.OrderDate = '20030401'
           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';
GO
SELECT ProductID FROM Production.ZeroInventory;