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

  • mcfarlandparkway

    SSCertifiable

    Points: 7635

    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 )

  • Mike01

    SSChampion

    Points: 11116

    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: 7635

    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: 11116

    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: 7635

    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: 11116

    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/

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

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