UGISS User Group Italiano SQL Server
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 TRAN
–(1) copia le righe interessate di [T_Source] in [T_Target] INSERT
T_Target (col1, col2, …)
SELECT
col1, col2, …
FROM
T_Source
WHERE
<where_condition>
–(2) elimina le righe interessate da [T_Source] DELETE FROM
T_Source
WHERE
<where_condition>
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
