none
Record Count on MERGE Statement on Insert,Update,Delete

    Question

  • HI All,

    How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 

    Thanks.

     


    khrizz tell
    Thursday, November 25, 2010 10:52 AM

Answers

  • You need an OUTPUT clause, try this:

     

    DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));
    
    MERGE tblTarget AS Target
    USING (SELECT Col1,Col2 FROM tblSource) 
    	AS Source
    ON (Target.Col1 = Source.Col1)
    WHEN MATCHED THEN 
    	UPDATE SET target.Col2 = source.Col2 -- Need to get affected rows here
    WHEN NOT MATCHED BY TARGET THEN
    	INSERT (Col1,Col2) VALUES (Col1,Col2); -- Need to get affected rows here
    OUTPUT $action INTO @SummaryOfChanges;
    
    SELECT Change, COUNT(*) AS CountPerChange
    FROM @SummaryOfChanges
    GROUP BY Change;
    


    ~Manoj http://sqlwithmanoj.com
    Thursday, November 25, 2010 11:54 AM
  •  

    1. Create a temp table like

     CREATE table #t
     ( ChangeType VARCHAR(100),
     InputID int,
     DeletedId int
     )

    2. Write Merge Statement  and in the end use $output something like

     MERGE INTO TARGET
    USING (SELECT * from Source) AS SOURCE
    ON Target.ID = Source.ID
    WHEN -- upadate
        MATCHED and Target.Name <> Source.Name THEN
            UPDATE
            SET Name = SOURCE.Name
    WHEN -- new record in source
        NOT MATCHED BY TARGET THEN
            INSERT  ( ID ,Name)
            VALUES ( Source.ID,Source.Name)
    WHEN --records deleted in source
        NOT MATCHED BY SOURCE THEN
            DELETE
    --see action
    OUTPUT $action
        , Inserted.ID AS InsertedID
        , Deleted.ID AS DeletedID
     into #t;

     
     select * from #t

     

    3. now from #t you can get count and your changed ids.

    For example check out

    http://sqlserversolutions.blogspot.com/2010/11/merge-statement-in-sql-server-2008.html

     


    Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/
    • Marked as answer by Khrizz_tell Thursday, November 25, 2010 1:51 PM
    Thursday, November 25, 2010 11:55 AM

All replies

  • For Example 
    create table xx
    (a int ,
    b int)
    
    declare @i int
    
    insert into xx
    select 1,1
    union all
    select 1,2
    union all
    select 2,1
    
    select @i = @@rowcount
    
    select @i
    
    delete from xx
    where a = 1
    select @i = @@rowcount
    select * from xx
    
    update xx
    set b = 5
    where a = 2
    select @i = @@rowcount
    

    pass on the variable @i to ur application ...

    HI All,

    How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 

    Thanks.

     


    khrizz tell

    use following statement just after u do ur insert update delete etc .. weather in ur sp .. ssms query etc 

    select @@rowcount


    Hope that helps ... Kunal
    • Edited by Kunal Joshi Thursday, November 25, 2010 11:14 AM code added for help
    Thursday, November 25, 2010 11:01 AM
  • @@rowcount is used to get affect rows in the basic insert,update, delete,select statements, what I need is to get the affected rows inside the merge statement for example :

    MERGE tblTarget AS Target
    USING (SELECT Col1,Col2 FROM tblSource) AS Source
    ON (Target.Col1 = Source.Col1)
    WHEN MATCHED THEN 
    UPDATE SET target.Col2 = source.Col2 -- Need to get affected rows here
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (Col1,Col2) VALUES (Col1,Col2); -- Need to get affected rows here
    

    I've already tried to use @@rowcount but its not working 

     


    khrizz tell
    Thursday, November 25, 2010 11:42 AM
  • Use the OUTPUT clause to select the special column $Action into a table variable. The topic of MERGE in Books Online has more details on this column.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Thursday, November 25, 2010 11:54 AM
  • You need an OUTPUT clause, try this:

     

    DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));
    
    MERGE tblTarget AS Target
    USING (SELECT Col1,Col2 FROM tblSource) 
    	AS Source
    ON (Target.Col1 = Source.Col1)
    WHEN MATCHED THEN 
    	UPDATE SET target.Col2 = source.Col2 -- Need to get affected rows here
    WHEN NOT MATCHED BY TARGET THEN
    	INSERT (Col1,Col2) VALUES (Col1,Col2); -- Need to get affected rows here
    OUTPUT $action INTO @SummaryOfChanges;
    
    SELECT Change, COUNT(*) AS CountPerChange
    FROM @SummaryOfChanges
    GROUP BY Change;
    


    ~Manoj http://sqlwithmanoj.com
    Thursday, November 25, 2010 11:54 AM
  •  

    1. Create a temp table like

     CREATE table #t
     ( ChangeType VARCHAR(100),
     InputID int,
     DeletedId int
     )

    2. Write Merge Statement  and in the end use $output something like

     MERGE INTO TARGET
    USING (SELECT * from Source) AS SOURCE
    ON Target.ID = Source.ID
    WHEN -- upadate
        MATCHED and Target.Name <> Source.Name THEN
            UPDATE
            SET Name = SOURCE.Name
    WHEN -- new record in source
        NOT MATCHED BY TARGET THEN
            INSERT  ( ID ,Name)
            VALUES ( Source.ID,Source.Name)
    WHEN --records deleted in source
        NOT MATCHED BY SOURCE THEN
            DELETE
    --see action
    OUTPUT $action
        , Inserted.ID AS InsertedID
        , Deleted.ID AS DeletedID
     into #t;

     
     select * from #t

     

    3. now from #t you can get count and your changed ids.

    For example check out

    http://sqlserversolutions.blogspot.com/2010/11/merge-statement-in-sql-server-2008.html

     


    Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/
    • Marked as answer by Khrizz_tell Thursday, November 25, 2010 1:51 PM
    Thursday, November 25, 2010 11:55 AM
  • This worked with modification , Replace ID with column name existing in the target table
    
    Like this
    
    OUTPUT $action
       , Inserted.Col1 AS InsertedID
      , Deleted.Col1 AS DeletedID
     into #t; 
    Thanks to all your help! 

     

    OUTPUT $action
        , Inserted.ID AS InsertedID
        , Deleted.ID AS DeletedID
     into #t;


    Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/


    khrizz tell
    Thursday, November 25, 2010 1:51 PM
  • You could wrap the select ... count(*)... GROUP BY statement at the end around the merge sytax to directly set a variable to the OUTPUT $action field. This approach can also be used for creating new rows in a Type 2 Slowly changing dimensions where the "WHEN MATCHED" section is used to update (expire) the existing dimension member, with the output clause insert a new row with the updated attributes.
    My Blog "Karl Beran's BI Mumble"
    Thursday, November 25, 2010 2:42 PM