Posso spostare un set righe con un'unica istruzione?

L’uso della calusola OUTPUT nel comando di DELETE, fornisce una valida e più compatta alternativa allo spostamento transazionale di un set di righe.

Date due tabelle [T_Source] e [T_Target], con la frase “spostare un set di righe da T_Source a T_Target” intendiamo la sequenza di operazioni:

1. copia le righe interessate di [T_Source] in [T_Target]
2. elimina le righe interessate da [T_Source]

Il tutto compreso in una transazione con gestione dell’errore.

In T-SQL (a partire da SQL 2005) si potrebbe schematizzare così:

  BEGIN TRY     BEGIN TRAN       –(1) copia le righe interessate di [T_Source] in [T_Target]       INSERT         T_Target (col1, col2, …)       SELECT         col1, col2, …       FROM         T_Source       WHERE

      –(2) elimina le righe interessate da [T_Source]       DELETE FROM         T_Source       WHERE

    COMMIT TRAN   END TRY   BEGIN CATCH      ROLLBACK TRAN   END CATCH

La gestione dell’errore insieme alla transazione esplicita mi garantiscono la riuscita di entrambe le istruzioni (la INSERT prima e la DELETE dopo), o il loro annullamento in caso di errore. L’uso della calusola OUTPUT nel comando di DELETE, fornisce una valida e più compatta alternativa a questo codice. Vediamo un esempio:

use tempdb go

/*     preparo il “terreno” nel tempdb all’esempio */

if object_id(‘dbo.T1’) is not null     drop table T1 go if object_id(‘dbo.T2’) is not null     drop table T2 go

/*     creo la tabella T1 nel tempdb     e la popolo con qualche riga */

create table T1 (col1 int primary key, col2 varchar(30)) go

insert T1 values (1, ‘uno’) insert T1 values (2, ‘due’) insert T1 values (3, ’tre’) insert T1 values (4, ‘quattro’) go

/*     con una SELECT..INTO..FROM..WHERE 1=0     creo la tabella T2 con uguale struttura della T1     e con l’aggiunta delle colonne DATA_MODIFICA e UTENTE_MODIFICA */

select     T1.*,     data_modifica = convert(smalldatetime, NULL),     utente_modifica = convert(sysname, NULL) into     T2 from     T1 where     1=0 go

select * from T1 go select * from T2 go

/*     sposto le righe della T1 che hanno un valore pari in col1,     nella tabella T2 mediante una DELETE FROM..OUTPUT..INTO..WHERE.     Il tutto in 1 transazione unica! */

delete from     T1 output     deleted.col1,     deleted.col2,     getdate(),     user_name() into     T2 where     col1 % 2 = 0 go

select * from T1 go select * from T2 go

/*

Clean tables

*/

if object_id(‘dbo.T1’) is not null     drop table T1 go if object_id(‘dbo.T2’) is not null     drop table T2 go