none
Alternate queries for MERGE

    질문

  • 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

    2018년 7월 13일 금요일 오전 10:08

답변

  • Hi All,

    Thank you for the replies.

    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.

    ‌‌

    You would replace MERGE with normal INSERT and UPDATE statements 

    So for your case it would look like this

    UPDATE t
    SET t.Col1 = s.Col1,
    t.Col2 = s.COl2,
    ..,
    IsDeleted = 0
    FROM YourTable AS t
    INNER JOIN SourceTable AS s
    ON s.PK = t.PK
    
    INSERT YourTable (Col1,Col2,...,IsDeleted)
    SELECT Col1,Col2,...,0
    FROM SourceTable s
    WHERE NOT EXISTS
    (
    SELECT 1
    FROM YourTable
    WHERE PK = s.PK
    )
    
    
    UPDATE t
    SET IsDeleted = 1
    FROM YourTable AS t
    WHERE NOT EXISTS
    (
    SELECT 1
    FROM SourceTable
    WHERE PK = t.PK
    )


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 표시됨 Samantha v 2018년 7월 16일 월요일 오전 10:28
    2018년 7월 14일 토요일 오전 5:38

모든 응답

  • You can use MERGE statement itself. for NOT MATCHED BY SOURCE condition just do UPDATE rather than DELETE

    Thats all you want

    so like

    MERGE INTO YourTable AS t
    USING SourceTable AS s
    ON s.PK = t.PK
    WHEN MATCHED 
    THEN UPDATE t SET col1=s.COl1,COl2=s.COl2,..,IsDeleted = 0
    WHEN NOT MATCHED BY TARGET
    THEN INSERT t (Col1,COl2,..,IsDeleted) VALUES (s.COl1,s.COl2,...,0)
    WHEN NOT MATCHED BY SOURCE
    THEN UPDATE t SET IsDeleted = 1
    ;
    

    the above is just a stub. Make sure you replace table names, column names correctly to convert into working query

    PK is primary key column of the tables. If its composite ensure you add all columns in the comparison


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 7월 13일 금요일 오전 11:07
  • You just need to use this instead of actual DELETE:

    WHEN blah THEN UPDATE SET IsDeleted = 1


    There is no requirement for a DELETE statement in MERGE.

    2018년 7월 13일 금요일 오전 11:15
    중재자
  • Hi All,

    Thank you for the replies.

    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.

    ‌‌

    2018년 7월 14일 토요일 오전 3:42
  • Hi All,

    Thank you for the replies.

    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.

    ‌‌

    You would replace MERGE with normal INSERT and UPDATE statements 

    So for your case it would look like this

    UPDATE t
    SET t.Col1 = s.Col1,
    t.Col2 = s.COl2,
    ..,
    IsDeleted = 0
    FROM YourTable AS t
    INNER JOIN SourceTable AS s
    ON s.PK = t.PK
    
    INSERT YourTable (Col1,Col2,...,IsDeleted)
    SELECT Col1,Col2,...,0
    FROM SourceTable s
    WHERE NOT EXISTS
    (
    SELECT 1
    FROM YourTable
    WHERE PK = s.PK
    )
    
    
    UPDATE t
    SET IsDeleted = 1
    FROM YourTable AS t
    WHERE NOT EXISTS
    (
    SELECT 1
    FROM SourceTable
    WHERE PK = t.PK
    )


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 표시됨 Samantha v 2018년 7월 16일 월요일 오전 10:28
    2018년 7월 14일 토요일 오전 5:38
  • There were problems with MERGE early on, but those have been resolved.  I merge millions of rows to  billions of row table every night without issue using MERGE in a DW.

    2018년 7월 16일 월요일 오전 11:06
    중재자