SQL Violation of PRIMARY KEY constraint (0x80131904)

1

I've only been using SQL Server for around two weeks and It's been going okay, although now I've become completely stumped on how to go about this issue I'm having.

I'll paste the query below, although it seems to be a bit of a mess (editing a query which was already in place)

INSERT INTO
CR126_ACCT_PRODS
(
CR126_ORG_CODE
, CR126_EXT_ACCT_CODE
, CR126_EXT_CNTCT_CODE
, CR126_OCCURENCE
, CR126_PROD_CODE
, CR126_OFFRD_DESRD
, CR126_SEARCH_1
, CR126_SEARCH_2
, CR126_SEARCH_3
, CR126_LONGITUDE
, CR126_LATITUDE
, CR126_UPD_USER_ID
, CR126_UPD_DATE
, CR126_ENT_USER_ID
, CR126_ENT_DATE
, CR126_DELETE_MARK
, CR126_USER_STS_01
, CR126_USER_STS_02
, CR126_USER_FLD_20X
, CR126_USER_FLD_08X
, CR126_USER_NBR_050P
, CR126_USER_NBR_092P
, CR126_USER_DATE_01
, CR126_ENT_TIME
, CR126_UPD_TIME
)
SELECT
INSERTED.CR126_ORG_CODE -- CR126_ORG_CODE - varchar(2)
, INSERTED.CR126_EXT_ACCT_CODE -- CR126_EXT_ACCT_CODE - varchar(8)
, '' -- CR126_EXT_CNTCT_CODE - varchar(8)
, 0 -- CR126_OCCURENCE - int
, CASE WHEN CR126_PROD_CODE = 'CS1' THEN 'CL1'
WHEN CR126_PROD_CODE = 'CS2' THEN 'CL2'
WHEN CR126_PROD_CODE = 'CS3' THEN 'CL3'
END
-- CR126_PROD_CODE - varchar(24)
, NULL -- CR126_OFFRD_DESRD - char(1)
, NULL -- CR126_SEARCH_1 - varchar(6)
, NULL -- CR126_SEARCH_2 - varchar(6)
, NULL -- CR126_SEARCH_3 - varchar(6)
, NULL -- CR126_LONGITUDE - int
, NULL -- CR126_LATITUDE - int
, 'TEST' -- CR126_UPD_USER_ID - varchar(10)
, GETDATE() -- CR126_UPD_DATE - datetime
, 'TEST' -- CR126_ENT_USER_ID - varchar(10)
, GETDATE() -- CR126_ENT_DATE - datetime
, NULL -- CR126_DELETE_MARK - char(1)
, NULL -- CR126_USER_STS_01 - char(1)
, NULL -- CR126_USER_STS_02 - char(1)
, NULL -- CR126_USER_FLD_20X - varchar(20)
, NULL -- CR126_USER_FLD_08X - varchar(8)
, NULL -- CR126_USER_NBR_050P - int
, NULL -- CR126_USER_NBR_092P - numeric
, NULL -- CR126_USER_DATE_01 - datetime
, GETDATE() -- CR126_ENT_TIME - datetime
, GETDATE() -- CR126_UPD_TIME - datetime
FROM
INSERTED
WHERE
CR126_PROD_CODE IN ('CS1', 'CS2', 'CS3')
END

What I'm trying to do is;

  1. When CS1 is input, it will also choose CL1, along with CS2 for CL2, CS3 for CL3 and so forth.
  2. When choose CS1 and CL1 is already present, I don't want it to try and input CL1 again

So far I've got the first part working, although when I remove CS1 and input it again, it'll try to input CL1 again which generates this error.

I know there's something to do with joining, although I'm really not sure how to go about this.

I can offer more information or paste more of the query if it'd help - sorry if the question is a bit vague or not informative.

Thanks in advance.

2

Perhaps something like this

INSERT INTO MyDestinationTable
(
    Col1, Col2, ...
)
SELECT ColX, ColY, ...
FROM MySourceTable AS S
WHERE NOT EXISTS
(
    SELECT *
    FROM MyDestinationTable AS D
        WHERE D.ColX = S.Col1   --- This test is usually on the PKey column(s)
)