Post reply

TSQL queries for handling Merge statements

  • July 13, 2018 at 4:09 am

    #414368

    Hi All,

    How can we implement MERGE statememnt using TSQL. Reason, in our application we might need some custom logic to be written.
    For example, if we consider DELETE operation, it is not a row delete operation instead there is an column marked for deletion (IsDeleted). Kind of soft delete.

    So, looking for pure sql code, when comparing 2 identical tables with same structure (say srctbl and trgtbl) with some rows in both the tables, then I should be able to do below operations
    - INSERT newly added rows
    - UPDATE MATCHED rows based on key column
    - DELETE is not straignt forward. its not a direct DELETE from table. Its just an update or marking IsDeleted column as 1.

    If anyone has already worked on similar script please share or else looking for some urls to implement the same.

    Any example with a demo script will be a great help. So that I can customize the same as per need.

    Thanks,

    Sam

  • July 13, 2018 at 5:15 am

    #1997449

    This is all very well documented. If you can understand the query below, I do not understand why you would have a problem understanding the documentation.

    MERGE target WITH (HOLDLOCK) trg
    USING source src
    ON trg.keycol = src.keycol
    WHEN MATCHED AND NOT EXISTS
           (
             SELECT trg.nonkeycols
             INTERSECT
             SELECT src.nonkeycols
           ) THEN
      UPDATE SET trg.nonkeycols = src.nonkeycols
    WHEN NOT MATCHED THEN
      INSERT
      (
    cols
      )
      VALUES
      (
       src.cols
      )
    WHEN NOT MATCHED BY SOURCE THEN
      UPDATE SET trg.IsDeleted = 1;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • July 13, 2018 at 9:00 pm

    #1997576

    With all the problems functional and performance they previously had with MERGE, I'm still skeptical about MERGE and I wouldn't use MERGE if it where the last bit of T-SQL left on Earth.  What's wrong with the "old fashioned" method of building your own UPSERT?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 13, 2018 at 9:41 pm

    #1997579

    Even I heard some issues with MERGE when dealing with large sets of data w.r.t lock escalations and all..

    Suppose I had to write the logic using plain tsql, what will be logic for it. Did someone already implemented it ? any reusable script available or any link to related blog post please let me know.

  • July 13, 2018 at 10:20 pm

    #1997582

    vsamantha35 - Friday, July 13, 2018 9:41 PM

    Even I heard some issues with MERGE when dealing with large sets of data w.r.t lock escalations and all..

    Suppose I had to write the logic using plain tsql, what will be logic for it. Did someone already implemented it ? any reusable script available or any link to related blog post please let me know.

    It's just a kind of separation of what you'd do for MERGE.  You would write a separate (possibly outer) joined INSERT, UPDATE, and DELETE with the right condition.  You can sometimes substitute a WHERE NOT EXISTS that uses a correlated subquery to do the negative testing for INSERT.  Give it a try on your own.  I'm sure that you'll be able to science it out and it'll help you remember how to do it in the future.

    Of course, Phil's MERGE should also work just fine.  Don't let my learned-paranoia keep you from using the tool now that they've shaken the bugs out of the rug.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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