La clausola OUTPUT è stata introdotta nella versione SQL Server 2005. La clausola OUTPUT restituisce i valori di ogni riga interessata da istruzioni INSERT, UPDATE o DELETE. Supporta anche l’istruzione MERGE, che è stata introdotta in SQL Server 2008. Il risultato della clausola OUTPUT può essere inserito in una tabella separata durante l’esecuzione della query. Questa clausola è più comunemente utilizzata a scopo di audit. Usando questo qualcuno può implementare un processo personalizzato che funzionerebbe come CDC o agire allo stesso modo di CDC.

Inoltre, i risultati della clausola OUTPUT possono essere restituiti alle applicazioni di elaborazione per l’uso in cose come i messaggi di conferma, la registrazione e qualsiasi altro requisito dell’applicazione. In questo articolo, fornirò una serie di esempi per mostrare l’uso della clausola OUTPUT nell’acquisizione dei risultati delle righe aggiornate in una variabile di tabella per le istruzioni di AGGIORNAMENTO.

Guida introduttiva

Per seguire gli esempi seguenti, è necessario creare una tabella Department_SRC eseguendo il seguente codice T-SQL in un database SQL Server. La sezione di riferimento contiene Output_Update.file sql, che include il codice T-SQL per provare gli esempi seguenti.

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());

È possibile vedere nei dati della tabella che il record è stato inserito nella tabella come mostrato nell’immagine sottostante.

Implementare la Clausola OUTPUT in Istruzioni di Aggiornamento

Come abbiamo discusso nel mio articolo precedente, la clausola OUTPUT consente di accedere alle due tabelle virtuali (Magic Tabelle):

1. “INSERITO” contiene le nuove righe (di INSERIMENTO o di AGGIORNAMENTO del SET)
2. “CANCELLATI” che contiene la vecchia copia delle righe di AGGIORNAMENTO(SET)

La clausola OUTPUT con un’istruzione UPDATE ha accesso sia INSERITO e CANCELLATO le tabelle interne. Ciò significa che entrambe queste tabelle sono accessibili contemporaneamente all’esecuzione dell’istruzione UPDATE. Ad esempio, supponiamo che stiamo aggiornando alcune colonne in una tabella per una singola riga. I valori appena aggiornati sono le modifiche alla tabella e si trovano nella tabella interna INSERITA. Inoltre, i dati che sono stati modificati sono i vecchi valori di dati e vengono inseriti nella tabella interna ELIMINATA.

Visualizzazione dei valori di aggiornamento

Utilizzando la clausola OUTPUT, possiamo visualizzare i valori aggiornati nella finestra output selezionando i nomi delle colonne con il prefisso INSERITO o utilizzando INSERTED.* per visualizzare tutte le colonne della tabella INSERITA. Inoltre, possiamo visualizzare i vecchi valori dei dati dalla tabella nella finestra di output selezionando i nomi delle colonne con prefisso ELIMINATO o utilizzando DELETED.* per visualizzare tutte le colonne di quella tabella. Questo aiuterà anche a stampare alcuni dei messaggi di output nella finestra di management studio.

Per spiegare questo con un esempio, sto eseguendo il codice seguente, in cui stiamo cercando di aggiornare la colonna GroupName dal valore “Ricerca e sviluppo” al valore “IT” per il dipartimento “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'

Una volta eseguito il codice sopra, possiamo vedere il risultato della clausola OUTPUT come mostrato nell’immagine qui sotto.

Inserimento dei risultati di OUTPUT in una variabile di tabella

Per mostrare questo esempio, ho dichiarato una variabile di tabella chiamata @Updated per memorizzare i risultati della clausola OUTPUT in questa variabile di tabella. Usiamo l’OUTPUT IN sintassi per memorizzare i risultati in una variabile di tabella. In questo approccio, abbiamo accesso a impostare le righe sia i nuovi valori che i vecchi valori che sono stati aggiornati dalla query, che possono essere utilizzati per i passaggi successivi nello stesso 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

Una volta eseguito il codice sopra, possiamo vedere che i risultati sono stati inseriti nella variabile @Updated table come mostrato nell’immagine sottostante.

Uso della clausola OUTPUT per caricare una dimensione di TIPO 3 che cambia lentamente

Con gli esempi sopra mostrati, la clausola OUTPUT è un’ottima alternativa per caricare una tabella di dimensioni di TIPO 3. Per spiegare questo con un esempio, sto eseguendo il codice seguente, che creerà una tabella di dimensione di TIPO 3 chiamata 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 .

Una volta eseguito il codice sopra, possiamo vedere il risultato dei dati della tabella come mostrato nell’immagine qui sotto.

Mentre stiamo inserendo un nuovo record nella tabella Department_Type3, possiamo vedere che la colonna GroupName_old ha un valore NULL e GroupName_Current ha il valore dell’insert recente. Supponiamo che ci sia stato un cambiamento in GroupName_current per il nome del reparto “Engineering” e che sia necessario aggiornare la tabella Department_Type3 con il nuovo valore GroupName_current. Poiché si tratta di una tabella di dimensione TYPE3, il requisito qui è aggiornare la colonna GroupName_old con “Ricerca e sviluppo” e GroupName_Current con il nuovo valore.

--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 

Una volta eseguito il codice precedente, acquisiremo i risultati dalla tabella interna ELIMINATA nella variabile @Updated table e quindi utilizzeremo tali dati per aggiornare le colonne della tabella finale per implementare questa dimensione di TIPO 3 che cambia lentamente. Possiamo vedere il risultato dei dati della tabella finale prima e dopo l’esecuzione della dichiarazione di cui sopra, come mostrato nelle immagini qui sotto.

Prima dell’AGGIORNAMENTO di esecuzione dell’istruzione

Dopo l’AGGIORNAMENTO di esecuzione dell’istruzione

Clausola OUTPUT Limitazioni

Alcuni degli scenari in cui la clausola OUTPUT non è supportato:

1. DML di riferimento locale viste partizionate, viste partizionate distribuite, o tabelle remote.
2. INSERT istruzioni che contengono un’istruzione EXECUTE.
3. I predicati full-text non sono consentiti nella clausola OUTPUT quando il livello di compatibilità del database è impostato su 100.
4. La clausola OUTPUT INTO non può essere utilizzata per inserirla in una vista o in una funzione rowset.
5. Non è possibile creare una funzione definita dall’utente se contiene una clausola OUTPUT INTO che ha come destinazione una tabella

L’elenco completo è elencato in questo link:https://technet.microsoft.com/en-us/library/ms177564 (v=sql.110).aspx

Sommario

Come mostrato negli esempi precedenti, possiamo vedere che la clausola OUTPUT è facile da usare e può evitare molte codifiche personalizzate per catturare i risultati di output di valori aggiornati con valori vecchi e nuovi in una variabile di tabella durante l’esecuzione della query.