How to find out HOW MANY inserts, HOW MANY updates happend in merge

Question

Post reply

How to find out HOW MANY inserts, HOW MANY updates happend in merge

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
February 21, 2020 at 6:40 pm

#3727797

How can i get the counts of how many records updated and how many inserted with Merge

MERGE dbo.Student as Target
USING temp.student as Source
ON Target.Id = Source.Id
AND Target.Date = Source.Date
WHEN MATCHED AND (ISNULL(Target.StudentLocation,'') <> ISNULL(Source.StudentLocation,'')
OR ISNULL(Target.StudentCode,'') <> ISNULL(Source.StudentCode,''))
THEN UPDATE SET Target.StudentLocation = Source.StudentLocation ,
Target.StudentCode = Source.StudentCode,
Target.LfID = Source.LfID
WHEN NOT MATCHED
THEN INSERT (Id,Date,StudentLocation,StudentCode,LfID)
VALUES (Source.Id,Source.Date,Source.StudentLocation,Source.StudentCode,Source.LfID )

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

on a merge, you can use $Action in an output statement to get what was inserted vs updated

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

Is this correct?

DECLARE @tableVar TABLE (MergeAction VARCHAR(20), InsertedId NVARCHAR(50), UpdatedId NVARCHAR(50),InsertedDate date, UpdatedDate date)

MERGE dbo.Student as Target

USING temp.student as Source

ON Target.Id = Source.Id

AND Target.Date = Source.Date

WHEN MATCHED AND (ISNULL(Target.StudentLocation,'') <> ISNULL(Source.StudentLocation,'')

OR ISNULL(Target.StudentCode,'') <> ISNULL(Source.StudentCode,''))

THEN UPDATE SET Target.StudentLocation = Source.StudentLocation ,

Target.StudentCode = Source.StudentCode,

Target.LfID = Source.LfID

WHEN NOT MATCHED

THEN INSERT (Id,Date,StudentLocation,StudentCode,LfID)

VALUES (Source.Id,Source.Date,Source.StudentLocation,Source.StudentCode,Source.LfID )

OUTPUT

$action, inserted.Id 'inserted', updated.Id 'updated' , inserted.Date 'inserted' , updated.Date 'updated' INTO @tableVar

;

SELECT MergeAction, COUNT(*)

FROM @tableVar

GROUP BY MergeAction

Mike01 SSChampion Points: 11395 More actions · Answer 3

It looks correct. What did your testing show?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 4

I am getting this error

Msg 4104, Level 16, State 1, Line 31

The multi-part identifier "updated.Id" could not be bound.

Msg 4104, Level 16, State 1, Line 31

The multi-part identifier "updated.Date" could not be bound.

Mike01 SSChampion Points: 11395 More actions · Answer 5

The output statement is wrong. I should've looked closer. When you do an update, it puts the old data into deleted table and the new data into inserted. So it should be

OUTPUT

$action, inserted.Id , deleted.Id, inserted.Date , deleted.Date INTO @tableVar

;

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/