Paul Mauriello
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.TagSummaryExtende d, '-1') <> ISNULL(s.TagSummaryExtende d, '-1')) THEN
UPDATE
SET
TagSummary = ISNULL(NULLIF(s.TagSummary ,''), t.TagSummary),
TagSummaryExtended = ISNULL(NULLIF(s.TagSummary Extended,' '), t.TagSummaryExtended),
IsActive = CASE
WHEN NULLIF(s.TagSummary,'') IS NOT NULL OR NULLIF(s.TagSummaryExtende d,'') IS NOT NULL THEN 1
ELSE 0
END,
IsDelete = 0,
ModifiedBy = @UserID,
ModifiedDate = GETUTCDATE()
OUTPUT 'tblTagObjectSummary', $Action, NULL, Inserted.TagObjectSummaryI D INTO @tblMergeResults;
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.TagSummaryExtende
UPDATE
SET
TagSummary = ISNULL(NULLIF(s.TagSummary
TagSummaryExtended = ISNULL(NULLIF(s.TagSummary
IsActive = CASE
WHEN NULLIF(s.TagSummary,'') IS NOT NULL OR NULLIF(s.TagSummaryExtende
ELSE 0
END,
IsDelete = 0,
ModifiedBy = @UserID,
ModifiedDate = GETUTCDATE()
OUTPUT 'tblTagObjectSummary', $Action, NULL, Inserted.TagObjectSummaryI
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
YOU ROCK!!
ASKER
SELECT TOP 1
objectId,
dbo.udsfGetDictionaryID('T
dbo.udsfGetTags(scopeName,
dbo.udsfGetTags(scopeName,
FROM
@TagObject
)