question

YuliaFei-9484 avatar image
1 Vote"
YuliaFei-9484 asked

[MDS] Can we remove Primary Key of @BadMemberCodes in stg.udp_xxx_Leaf

When checking the procedure proc stg.udp_xxx_Leaf, I think the primary key in table variable seems not that necessary. And it is causing error: Violation of PRIMARY KEY constraint 'PK_#A3BA16B_D12D3D3F6A8BFF45'. Cannot insert duplicate key in object 'dbo.@BadMemberCodes'. The duplicate key value is (2010638).

The primary key is in below table variable:

DECLARE @BadMemberCodes TABLE
(
Staging_ID INT PRIMARY KEY
)

This table variable is just to get the Staging_ID in order to update table stg.XXX_Leaf. That is why I think it is not necessary. Can we permanently remove it from MDS design?

Below is how the table variable is populated:

INSERT INTO #MembersToRemove(Member_ID, Staging_ID, MemberCode, IsPurge, SetToNull, IsActive)
SELECT en.ID
, stgl.ID
, stgl.Code
, CASE WHEN stgl.ImportType IN (4/Purge/, 6/PurgeSetNullToRef/) THEN 1 ELSE 0 END -- IsPurge
, CASE WHEN stgl.ImportType IN (5/DeleteSetNullToRef/, 6/PurgeSetNullToRef/) THEN 1 ELSE 0 END -- SetToNull
, CASE WHEN en.Status_ID = 1/Active/ THEN 1 ELSE 0 END -- IsActive
FROM [stg].[Product_Leaf] stgl
LEFT JOIN mdm.[tbl_2_7_EN] en
ON en.Version_ID = @Version_ID
AND stgl.Code = en.Code
WHERE stgl.ImportType IN (3/Delete/, 4/Purge/, 5/DeleteSetNullToRef/, 6/PurgeSetNullToRef/)
AND stgl.ImportStatus_ID = 0-- Default
AND stgl.Batch_ID = @Batch_ID
and en.Status_ID <> 1

DELETE #MembersToRemove
OUTPUT deleted.Staging_ID
INTO @BadMemberCodes
WHERE Member_ID IS NULL
OR ( IsPurge = 0 -- Cannot delete an already deleted member
AND IsActive = 0)

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers