Link to home
Start Free TrialLog in
Avatar of Paul Mauriello
Paul MaurielloFlag for United States of America

asked on

Can someone help me rewrite this SQL Merge into SQL Inserts and Updates?

MERGE INTO dbo.tblTagObjectSummary t
USING cte_summary s ON s.ObjectID = t.ObjectID AND s.ScopeDictionaryID = t.ScopeDictionaryID

WHEN NOT MATCHED BY TARGET THEN
      INSERT (ScopeDictionaryID, ObjectID, TagSummary, TagSummaryExtended, CreatedBy) VALUES (s.ScopeDictionaryID, s.ObjectID, s.TagSummary, s.TagSummaryExtended, @UserID)

WHEN NOT MATCHED BY SOURCE AND t.IsActive = 1 AND t.IsDelete = 0 AND EXISTS(SELECT 1 FROM cte_summary aa WHERE aa.objectId = t.ObjectID AND aa.ScopeDictionaryID = t.ScopeDictionaryID) THEN
      UPDATE
      SET
            IsActive = 0,
            ModifiedBy = @UserID,
            ModifiedDate = GETUTCDATE()

WHEN MATCHED AND (ISNULL(t.TagSummary, '-1') <> ISNULL(s.TagSummary, '-1') OR ISNULL(t.TagSummaryExtended, '-1') <> ISNULL(s.TagSummaryExtended, '-1')) THEN

      UPDATE
      SET
            TagSummary = ISNULL(NULLIF(s.TagSummary,''), t.TagSummary),
            TagSummaryExtended = ISNULL(NULLIF(s.TagSummaryExtended,''), t.TagSummaryExtended),
            IsActive = CASE
                              WHEN NULLIF(s.TagSummary,'') IS NOT NULL OR NULLIF(s.TagSummaryExtended,'') IS NOT NULL THEN 1
                              ELSE 0
                          END,
            IsDelete = 0,
            ModifiedBy = @UserID,
            ModifiedDate = GETUTCDATE()

OUTPUT 'tblTagObjectSummary', $Action, NULL, Inserted.TagObjectSummaryID INTO @tblMergeResults;
ASKER CERTIFIED SOLUTION
Avatar of smilieface
smilieface
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Paul Mauriello

ASKER

;WITH cte_summary AS (

      SELECT TOP 1
            objectId,
            dbo.udsfGetDictionaryID('Tag Scope', scopeName) AS ScopeDictionaryID,
            dbo.udsfGetTags(scopeName, @UserCompanyID, objectId, 0, @Scope) AS TagSummary,
            dbo.udsfGetTags(scopeName, @UserCompanyID, objectId, 1, @Scope) AS TagSummaryExtended
      FROM
            @TagObject
)
YOU ROCK!!