Za pomocą instrukcji Output w operacjach DML (Data Manipulation Language) czyli: Delete, Insert, Update, Merge zapisujemy do tabeli informacje jakich wierszy dotyczyła modyfikacja.
W Merge dodatkowo jest znacznik $action, który opisuje czy w Merge wykonano delete, insert czy update.
-- przygotowanie tabel
DROP TABLE IF EXISTS #Loc_History
CREATE TABLE #Loc_History (
ObjectId int,
[Name] varchar(100),
operationType varchar(100),
ModifiedDate datetime)
DROP TABLE IF EXISTS #Loc_Delta
SELECT TOP 0 LocationID,[Name]
INTO #Loc_Delta
FROM [Production].[Location]
--1 INSERT i OUTPUT
INSERT INTO [Production].[Location] (
[Name],
[CostRate],
[Availability],
[ModifiedDate])
output
inserted.LocationID,
inserted.[Name],
'ADD',
getdate()
into #Loc_History (
ObjectId,
[Name],
operationType,
ModifiedDate)
select
'LocationTest' as [Name],
0 as [CostRate],
0 as [Availability],
getdate() as [ModifiedDate]
--2 UPDATE i OUTPUT
UPDATE L
set L.[Name] = 'New'
output
inserted.LocationID,
inserted.[Name],'MODIFY',
getdate()
into #Loc_History (
ObjectId,
[Name],
operationType,
ModifiedDate)
from
[Production].[Location] as L
where
L.LocationID = (select max(LocationID) from [Production].[Location])
--3 DELETE i OUTPUT
DELETE L
output
deleted.LocationID,
deleted.[Name],
'DELETE',
getdate()
into #Loc_History (
ObjectId,
[Name],
operationType,
ModifiedDate)
from
[Production].[Location] as L
where
L.LocationID = (select max(LocationID) from [Production].[Location])
--4 MERGE i OUTPUT
MERGE INTO #Loc_Delta as tgt
using (select top 1 * from [Production].[Location]) as src
on tgt.Locationid = src.Locationid
when not matched then
insert ([Name])
values (src.[Name])
when matched then
update set tgt.[Name] = src.[Name]
output
case when $action = 'INSERT' then inserted.LocationID else deleted.LocationID end,
case when $action = 'INSERT' then inserted.[Name] else deleted.[Name] end,
$action,
getdate()
into #Loc_History (
ObjectId,
[Name],
operationType,
ModifiedDate);