Using the OUTPUT clause with INSERT,UPDATE, DELETE
select * from employee;
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
DECLARE @EmployeeChanges TABLE (
DeletedName nvarchar(50),
InsertedName nvarchar(50)
)
UPDATE employee
SET first_Name = 'H'
OUTPUT DELETED.first_Name,
INSERTED.first_Name
INTO @EmployeeChanges
Output
SELECT DeletedName,
InsertedName
FROM @EmployeeChanges
DeletedName InsertedName
-------------------------------------------------- --------------------------------------------------
Jason H
Alison H
James H
Celia H
Robert H
Linda H
David H
James H
Joan H
