Paul Mauriello
asked on
Can someone help me rewrite this from SQL Merge to SQL UPDATE and INSERT
;WITH cte_source AS (
SELECT a.tagObjectId, a.objectId, a.tagId, a.tagCategoryScopeId, a.isLocked, c.IsExclusive
FROM
@TagObject a
JOIN dbo.vwTagCategoryScope b ON a.tagCategoryScopeId = b.TagCategoryScopeID
JOIN dbo.vwTagCategorySetting c ON b.CategoryDictionaryID = c.CategoryDictionaryID AND b.UserCompanyID = c.UserCompanyID
WHERE
c.IsActive = 1 AND
b.UserCompanyID = @UserCompanyID AND
b.ScopeDictionaryID = @ScopeDictionaryID
)
MERGE INTO dbo.tblTagObject t
USING cte_source
-- so basically here is how it works
-- if we match on primary key then no brainer just perform an update
-- if we match on CategoryScope, ObjectID, TagID or Category is exclusive then update
-- otherwise insert
s ON
t.IsActive = 1 AND
t.IsDelete = 0 AND (
s.tagObjectId = t.TagObjectID OR (
s.tagCategoryScopeId = t.TagCategoryScopeID AND s.objectId = t.ObjectID AND (
s.tagId = t.TagID OR s.IsExclusive = 1
)
)
)
WHEN NOT MATCHED BY TARGET THEN
INSERT (TagCategoryScopeID, TagID, ObjectID, IsLocked, CreatedBy) VALUES (s.tagCategoryScopeId, s.tagId, s.objectId, s.isLocked, @UserID)
WHEN NOT MATCHED BY SOURCE AND t.IsActive = 1 AND t.IsDelete = 0 AND (t.ObjectID = @ObjectID OR EXISTS(SELECT 1 FROM cte_source aa WHERE aa.objectId = t.ObjectID))
AND (
EXISTS (
SELECT
1
FROM
dbo.tblTagCategoryScope aa
WHERE
aa.IsDelete = 0 AND
aa.UserCompanyID = @UserCompanyID AND
aa.TagCategoryScopeID = t.TagCategoryScopeID AND
aa.ScopeDictionaryID = @ScopeDictionaryID
)
AND (@IsRecursive = 0 OR (@IsRecursive = 1 AND t.IsLocked = 0))
) THEN
UPDATE SET IsActive = 0, ModifiedBy = @UserID, ModifiedDate = GETUTCDATE()
WHEN MATCHED THEN
UPDATE SET TagID = s.tagId, IsLocked = s.isLocked, IsActive = 1, IsDelete = 0, ModifiedBy = @UserID, ModifiedDate = GETUTCDATE()
OUTPUT 'tblTagObject', $Action, NULL, Inserted.TagObjectID INTO @tblMergeResults;
SELECT a.tagObjectId, a.objectId, a.tagId, a.tagCategoryScopeId, a.isLocked, c.IsExclusive
FROM
@TagObject a
JOIN dbo.vwTagCategoryScope b ON a.tagCategoryScopeId = b.TagCategoryScopeID
JOIN dbo.vwTagCategorySetting c ON b.CategoryDictionaryID = c.CategoryDictionaryID AND b.UserCompanyID = c.UserCompanyID
WHERE
c.IsActive = 1 AND
b.UserCompanyID = @UserCompanyID AND
b.ScopeDictionaryID = @ScopeDictionaryID
)
MERGE INTO dbo.tblTagObject t
USING cte_source
-- so basically here is how it works
-- if we match on primary key then no brainer just perform an update
-- if we match on CategoryScope, ObjectID, TagID or Category is exclusive then update
-- otherwise insert
s ON
t.IsActive = 1 AND
t.IsDelete = 0 AND (
s.tagObjectId = t.TagObjectID OR (
s.tagCategoryScopeId = t.TagCategoryScopeID AND s.objectId = t.ObjectID AND (
s.tagId = t.TagID OR s.IsExclusive = 1
)
)
)
WHEN NOT MATCHED BY TARGET THEN
INSERT (TagCategoryScopeID, TagID, ObjectID, IsLocked, CreatedBy) VALUES (s.tagCategoryScopeId, s.tagId, s.objectId, s.isLocked, @UserID)
WHEN NOT MATCHED BY SOURCE AND t.IsActive = 1 AND t.IsDelete = 0 AND (t.ObjectID = @ObjectID OR EXISTS(SELECT 1 FROM cte_source aa WHERE aa.objectId = t.ObjectID))
AND (
EXISTS (
SELECT
1
FROM
dbo.tblTagCategoryScope aa
WHERE
aa.IsDelete = 0 AND
aa.UserCompanyID = @UserCompanyID AND
aa.TagCategoryScopeID = t.TagCategoryScopeID AND
aa.ScopeDictionaryID = @ScopeDictionaryID
)
AND (@IsRecursive = 0 OR (@IsRecursive = 1 AND t.IsLocked = 0))
) THEN
UPDATE SET IsActive = 0, ModifiedBy = @UserID, ModifiedDate = GETUTCDATE()
WHEN MATCHED THEN
UPDATE SET TagID = s.tagId, IsLocked = s.isLocked, IsActive = 1, IsDelete = 0, ModifiedBy = @UserID, ModifiedDate = GETUTCDATE()
OUTPUT 'tblTagObject', $Action, NULL, Inserted.TagObjectID INTO @tblMergeResults;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are most welcome! Glad to help.
ASKER