Hi all,
I'm executing a staging procedure to process data that I've just inserted into staging table. Every time I tryed to execute the procedure, the process is "freezing" on update below:
UPDATE stgl SET ErrorCode = stgl.ErrorCode | 2 FROM [stg].[Product_Leaf] stgl INNER JOIN [stg].[Product_Leaf] stgl2 ON stgl.Code = stgl2.Code AND stgl.ID
<> stgl2.ID AND stgl.Batch_ID = stgl2.Batch_ID WHERE stgl.ImportStatus_ID = 0 -- Default AND stgl.Batch_ID = @Batch_
I checked the code and created a solution that is running very fast:
IF LEN(@BatchTag) > 0 BEGIN
SELECT Code
into #Duplicated_Codes
FROM [stg].[Product_Leaf]
WHERE BatchTag = @BatchTag
AND ImportStatus_ID = 0 -- Default
GROUP BY Code
HAVING COUNT(1) > 1
UPDATE [stg].[Product_Leaf]
SET ErrorCode = ErrorCode | 2
WHERE Code IN(
SELECT Code
FROM #Duplicated_Codes)
AND BatchTag = @BatchTag
AND ImportStatus_ID = 0 -- Default
DROP TABLE #Duplicated_Codes
END; -- IF
ELSE IF @Batch_ID IS NOT NULL BEGIN
UPDATE stgl
SET ErrorCode = stgl.ErrorCode | 2
FROM [stg].[Product_Leaf] stgl
INNER JOIN [stg].[Product_Leaf] stgl2
ON stgl.Code = stgl2.Code
AND stgl.ID <> stgl2.ID
AND stgl.Batch_ID = stgl2.Batch_ID
WHERE stgl.ImportStatus_ID = 0 -- Default
AND stgl.Batch_ID = @Batch_ID;
END; -- IF
The problem is, every time we changed the structure on MDS entity the MDS recreates its correlated procedure (stg.udp_entity_leaf) and we lost the update that I did into the procedure.
My questions are,
There is any kind of path to solve this issue?
Am I doing something wrong? I mean, does anyone has this same issue?
I'm using the MDS for SQL Server 2016.
