Get Insert & Update counts from Merge $Action with outer query insert - SQL serv

Question

Post reply

Get Insert & Update counts from Merge $Action with outer query insert - SQL serv

shivas.sql

Old Hand

Points: 369
More actions
October 24, 2019 at 8:29 pm

#3691774
--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.
- This topic was modified 5 years, 10 months ago by shivas.sql.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

You could do the inserts and updates separately and then just interrogate @@ROWCOUNT ...

shivas.sql Old Hand Points: 369 More actions · Answer 2

hi phil, thanks for the reply, we cannot separate inserts and updates and need to continue use of merge in this requirement.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

This is an overly cumbersome query. Please explain why everything has to be done in the same code batch.

"This is a business requirement" is not an explanation.

shivas.sql Old Hand Points: 369 More actions · Answer 4

sorry for presenting it poorly, according to the technical requirement document we cannot use separate inserts and updates and need to use Merge for better performance.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

shivas.sql wrote:

sorry for presenting it poorly, according to the technical requirement document we cannot use separate inserts and updates and need to use Merge for better performance.

If performance is the deciding factor, you may be in for a nasty shock.

Check this link, for example: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15

2019-10-24_16-26-04

ScottPletcher SSC Guru Points: 101200 More actions · Answer 6

You don't need multiple counts.

DROP TABLE IF EXISTS #actions;
CREATE TABLE #actions ( action nvarchar(10) NULL )

MERGE
...
OUTPUT $ACTION into #actions
...

DECLARE @insert_count int
DECLARE @update_count int

SELECT @insert_count = SUM(CASE WHEN action = 'INSERT' THEN 1 ELSE 0 END),
    @update_count = SUM(CASE WHEN action = 'UPDATE' THEN 1 ELSE 0 END)
FROM #actions

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

shivas.sql Old Hand Points: 369 More actions · Answer 7

hi scott, thanks for your reply, i am trying to perform SCD type 2 using merge and get insert, update counts. the solution which you provided supports only inserts in my case.