merge - 使用外部查询插入从 Merge $Action 获取插入和更新计数 - SQL Server

Question

--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'

问题

我们如何从 Merge $Action 获得插入和更新的总数？？我需要记录插入和更新计数。

   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 

业务限制：我们无法分离外部插入功能，我知道我们可以使用临时表将所有输出加载到其中并获取计数并执行外部插入。

标签： merge