--Inserting a new record for updates
Insert dbo.DestinationTable
(
CustomerID,
CustomerName,
IscurrentFlag
)
select
CustomerID,
CustomerName,
IscurrentFlag
from
(
--Inserting a new record for inserts
MERGE dbo.DestinationTable as dt
USING dbo.SourceTable as src
ON dt.CustomerID=src.CustomerID
WHEN NOT MATCHED THEN INSERT
(CustomerID,CustomerName,IscurrentFlag)
values (src.CustomerID,src.CustomerName,'Y')
WHEN MATCHED
THEN UPDATE
SET dt.IscurrentFlag ='N'
OUTPUT src.*, $Action as MergeAction
) as mrg
WHERE MergeAction = 'UPDATE'
problem
how can we get the total number of inserts and updates from Merge $Action ??, I need to log the insert and update counts.
I tried using multiple outputs inside merge but getting an error
"An OUTPUT INTO clause is not allowed in a nested INSERT, UPDATE, DELETE, or MERGE statement"
OUTPUT $Action as counts into @Temp
OUTPUT src.*, $Action as MergeAction
business restriction : we cannot separate the outer insert functionality, i know we can use a temp table to load all the OUTPUT into it and get counts and perform outer insert.
