locked
Merge Query RRS feed

  • Question

  • I`m running a Merge query I want to know is there a way after I run this query I can get a resulset which gives me

    the columns where the values have been updated? and when not matched what rows got inserted??? I want to use this

    as a tracking query??

    	 
    MERGE into Trg_Persons as a
    USING (select * from Src_Persons) as t ON a.PersonID = t.PersonID
    WHEN MATCHED THEN UPDATE set a.PersonID = t.PersonID,a.LastName=t.LastName,a.FirstName=t.FirstName,a.Address=t.Address,a.City=t.City
    WHEN NOT MATCHED THEN INSERT (PersonID,LastName,FirstName,Address,City) VALUES (PersonID,LastName,FirstName,Address,City); 
    select * from Trg_Persons
    order by PersonID


    Tuesday, June 17, 2014 5:15 PM

Answers

  • Refer the below link

    http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

    --Prashanth

    • Proposed as answer by Sugumar Pannerselvam Tuesday, June 17, 2014 5:30 PM
    • Marked as answer by Uaaqi Tuesday, June 17, 2014 6:11 PM
    Tuesday, June 17, 2014 5:18 PM

All replies

  • Refer the below link

    http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

    --Prashanth

    • Proposed as answer by Sugumar Pannerselvam Tuesday, June 17, 2014 5:30 PM
    • Marked as answer by Uaaqi Tuesday, June 17, 2014 6:11 PM
    Tuesday, June 17, 2014 5:18 PM
  • You may refer the below MSDN article:

    http://msdn.microsoft.com/en-in/library/bb510625.aspx

    The link has got very good example for  MERGE with OUTPUT which is the one you are after.

    Tuesday, June 17, 2014 5:21 PM
  • Hi Uaqi,

    For detials about MERGE AND OUTPUT Clause, Pls refer forums suggessted by Prashant and latheesh.

    As brief answer for your query.

    MERGE into Trg_Persons as a
    USING (select * from Src_Persons) as t ON a.PersonID = t.PersonID
    WHEN MATCHED THEN UPDATE set a.PersonID = t.PersonID,a.LastName=t.LastName,a.FirstName=t.FirstName,a.Address=t.Address,a.City=t.City
    WHEN NOT MATCHED THEN INSERT (PersonID,LastName,FirstName,Address,City) VALUES (PersonID,LastName,FirstName,Address,City) 
    OUTPUT $action, Inserted.*, Deleted.*;
    Bolder line will give the desired result for you.

    - Pls mark as answer or vote, if this post is helpful.

    Sugumar Pannerselvam 

     

    Tuesday, June 17, 2014 5:30 PM
  • Thanks Prashanth thats exactly what I was looking for
    Tuesday, June 17, 2014 6:11 PM