Basic demonstration
The following basic demonstration tries to explain how the output clause might be used.
Table
As usual with SQL examples, a table with some initial
data is needed:
create table tq84_table (
val_1 integer,
val_2 varchar(10),
val_3 varchar(10)
);
insert into tq84_table values ( 3, 'A', 'X');
insert into tq84_table values ( 5, 'B', 'Y');
insert into tq84_table values ( 9, 'C', 'Z');
go
Inserting a few values
The following insert statement inserts additional values into the table and additionally adds the inserted values to the declared table variable as well.
Especially note the inserted keyword that allows to refer to the new data:
declare
@insertedValues table (
V1 integer,
V2 varchar(10),
V3 varchar(10)
);
insert tq84_table
output
inserted.*
into
@insertedValues
select
10 + val_1,
val_2 + val_2,
val_3 + val_3
from
tq84_table;
select * from @insertedValues;
go
Updating a few values
Similarly, we're going to update some records in the table.
Because an update statement has before and after values, there is no updated keyword, but rather both, the deleted and inserted keywords that allow to refer to the respective old and new values:
declare
@someUpdatedValues table (
V1_before integer,
V1_after integer,
V2_before varchar(10),
v2_after varchar(10)
);
update tq84_table
set
val_1 = 20 + val_1 + 20,
val_2 = 'u' + val_2,
val_3 = 'v' + val_3
output
deleted. val_1,
inserted.val_1,
deleted. val_2,
inserted.val_2
into
@someUpdatedValues
where
val_1 > 7;
select * from @someUpdatedValues;
go