FWIW I did some playing... this is the script I used (pardon the JUNK prefix, it simply reminds me to clean this up when I am done)
IF OBJECT_ID ('JUNKDepartment_SRC', 'U') IS NOT NULL
DROP TABLE dbo.JUNKDepartment_SRC;
CREATE TABLE [dbo].[JUNKDepartment_SRC](
[DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL,
[GroupName] varchar(50) NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
declare @chgs table(
[DepartmentID] [smallint],
[Name] varchar(50) ,
[GroupName] varchar(50) ,
[ModifiedDate] [datetime]
)
Insert into [dbo].[JUNKDepartment_SRC]([Name],[GroupName],[ModifiedDate])
Values('Engineering','Research and Development','5/24/2017' );
insert into [dbo].[JUNKDepartment_SRC]([Name],[GroupName],[ModifiedDate])
output inserted.DepartmentID, inserted.name, inserted.GroupName, inserted.ModifiedDate
Values('Sales','Sales & Marketing',getdate());
-- this is something different I tried
update [dbo].[JUNKDepartment_SRC]
set [GroupName] = 'Sales, Marketing & Promos'
output inserted.* into @chgs
where [DepartmentID] = 2
delete [dbo].[JUNKDepartment_SRC]
output deleted.* into @chgs
where [DepartmentID] = 1
select * from @chgs
-- the last query returns this
DepartmentID Name GroupName ModifiedDate
2 Sales Sales, Marketing & Promos 2017-05-25 11:36:18.830
1 Engineering Research and Development 2017-05-24 00:00:00.000
It works on UPDATE too. Enjoy