70-433 SQL Server OUTPUT clause / the INSERTED and DELETED prefixes

OUTPUT clause you can see before and after values of field xyz by using the INSERTED and DELETED prefixes, for example INSERTED.xyz and DELETED.xyz. Alternatively INSERTED.* for all fields

You can have two levels of OUTPUT within an insert statement, you start by inserting into a table, which automatic triggers a new ID field (OUTPUT employee.empID) which is inserted into "table variable" which has it own OUTPUT clause.. this can be added to log the times of transactions (GETDATE()) even though these times aren't necessarily stored in the database.

USE tempdb
 
go
 

 
CREATE TABLE table1
 
(
 
    id INT,
 
    employee VARCHAR(32)
 
)
 
go
 

 
INSERT INTO table1 VALUES(1, 'Fred')
 
INSERT INTO table1 VALUES(2, 'Tom')
 
INSERT INTO table1 VALUES(3, 'Sally')
 
INSERT INTO table1 VALUES(4, 'Alice')
 
GO
 

 
DECLARE @MyTableVar TABLE
 
(
 
    id INT,
 
    employee VARCHAR(32)
 
)
 

 
PRINT 'table1, before delete' 
 
SELECT * FROM table1
 

 
DELETE FROM table1
 
OUTPUT DELETED.* INTO @MyTableVar
 
WHERE id = 4 OR id = 2
 

 
PRINT 'table1, after delete'
 
SELECT * FROM table1
 

 
PRINT '@MyTableVar, after delete'
 
SELECT * FROM @MyTableVar
 

 
DROP TABLE table1
 

 
--Results
 
--table1, before delete
 
--id          employee
 
------------- ------------------------------
 
--1           Fred
 
--2           Tom
 
--3           Sally
 
--4           Alice
 
--
 
--table1, after delete
 
--id          employee
 
------------- ------------------------------
 
--1           Fred
 
--3           Sally
 
--@MyTableVar, after delete
 
--id          employee
 
------------- ------------------------------
 
--2           Tom
 
--4           Alice

http://msdn.microsoft.com/en-us/library/ms177564(SQL.90).aspx