Another T-SQL enhancement in SQL Server 2008 applies to the use of the OUTPUT clause. The OUTPUT clause allows you to return data from a modification statement (INSERT, UPDATE, MERGE, or DELETE)
as a result set or into a table variable or an output table. In SQL
Server 2008, you can include one of these Data Manipulation Language
(DML) statements with an OUTPUT clause within the context of an INSERT...SELECT statement.
In the MERGE statement in this Listing, the OUTPUT
clause was used to display the rows affected by the statement. Suppose
that you want the output of this to be put into a separate audit or
processing table. In SQL Server 2008, you can do so by allowing the MERGE statement with the OUTPUT clause to be incorporated as a derived table in the SELECT clause of an INSERT statement.
To demonstrate this approach, you first need to create a table for storing that data:
if OBJECT_ID('inventory_audit') is not null
drop table inventory_audit
go
CREATE TABLE inventory_audit
(
Action varchar(10) not null,
Src_title_id varchar(6) null,
Src_qty int null,
Tgt_title_id varchar(6) null,
Tgt_qty int null,
Loginname varchar(30) null default suser_name(),
Action_DT datetime2 null default sysdatetime()
)
Now it is possible to be put a SELECT statement atop the MERGE command as the values clause for an INSERT into the inventory_audit table (see Listing 2).
Listing 2. Insert over DML Example
-- NOTE: to see the results for this example
-- you first need to clear out and repopulate
-- the store_inventory table
Truncate table store_inventory
Insert store_inventory (stor_id, title_id, qty, update_dt)
select stor_id, title_id, qty = SUM(qty), update_dt = GETDATE()
from sales s
where stor_id = 'A011'
group by stor_id, title_id
go
insert inventory_audit
(action,
Src_title_id,
Src_qty ,
Tgt_title_id,
Tgt_qty ,
Loginname,
Action_DT
)
select *, SUSER_NAME(), SYSDATETIME()
from (
MERGE
INTO store_inventory as s
USING inventory_load as i
ON s.stor_id = i.stor_id
and s.title_id = i.title_id
WHEN MATCHED and s.qty <> i.qty
THEN UPDATE
SET s.qty = i.qty,
update_dt = getdate()
WHEN NOT MATCHED
THEN INSERT (stor_id, title_id, qty, update_dt)
VALUES (i.stor_id, i.title_id, i.qty, getdate())
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action,
isnull(inserted.title_id, '') as src_titleid,
isnull(str(inserted.qty, 5), '') as src_qty,
isnull(deleted.title_id, '') as tgt_titleid,
isnull(str(deleted.qty, 5), '') as tgt_qty
) changes ( action,
Src_title_id,
Src_qty ,
Tgt_title_id,
Tgt_qty );
go
select * from inventory_audit
go
Action Src_title_id Src_qty Tgt_title_id Tgt_qty Loginname Action_DT
------ ------------ ------- ------------ ------- --------- ----------------------
INSERT BU2075 1536 0 rrankins 2010-04-02 22:20:59.48
DELETE 0 CH3348 24 rrankins 2010-04-02 22:20:59.48
INSERT CH5390 888 0 rrankins 2010-04-02 22:20:59.48
INSERT CH7553 540 0 rrankins 2010-04-02 22:20:59.48
INSERT FI1950 1308 0 rrankins 2010-04-02 22:20:59.48
INSERT FI2100 1104 0 rrankins 2010-04-02 22:20:59.48
INSERT FI3822 996 0 rrankins 2010-04-02 22:20:59.48
UPDATE FI4970 1632 FI4970 1140 rrankins 2010-04-02 22:20:59.48
INSERT FI7040 1596 0 rrankins 2010-04-02 22:20:59.48
INSERT LC8400 732 0 rrankins 2010-04-02 22:20:59.48
DELETE 0 NF8918 1140 rrankins 2010-04-02 22:20:59.48
