Violation of PRIMARY KEY constraint when using cross apply and outer apply together

Question

I was using Microsoft SQL Server. I want to use both functions to parse data going into my table. So I use cross apply and outer apply together.

CROSS APPLY CA_Parse_CorpActnDtls_fn(MessageID) ent
outer apply CA_Parse_CorpActnOptnDtls_fn(ev.MessageID) cod

But when I did this it complain about the following erro:

Violation of PRIMARY KEY constraint 'PK_AfterParse_CA_Events'. Cannot insert duplicate key in object 'dbo.AfterParse_CA_Events'. The duplicate key value is (105818432, 37819929). The statement has been terminated.

The whole T-sql code looks like :

insert into AfterParse_CA_Events (
           EventID
           ,MessageID
          ,cdtprFunction
          ,CreationDate
          ,MsgDefIdr
          ,EventType
          ,CFI
          ,EventProcessingType
          ,MndtryVlntryEvtTp
          ,RecordDate
          ,EffectiveDate
          ,DueBillRdmDate
          ,CUSIP
          ,LSCI_DateOfRecord
          ,RoundingDesc

        )

    SELECT  ent.EventID
            ,ent.MessageID
            ,ent.cdtprFunction
            ,ent.CreationDate
            ,ent.MsgDefIdr
            ,ent.EventType
            ,ent.CFI
            ,ent.EventProcessingType
            ,ent.MndtryVlntryEvtTp
            ,ent.RecordDate
            ,ent.EffectiveDate_Cmpny
            ,ent.DueBillRdmDate
            ,ent.CUSIP
            ,ROXSQL.dbo.GetNthTradeDay_fn(
            case when ent.EventProcessingType = 'DISN'
                then COALESCE (ent.ExDividendDate, ent.RecordDate)
                ELSE COALESCE(ent.EffectiveDate_Xchg, ent.EffectiveDate_Cmpny,cod.EarliestPaymentDate_Secu,cod.PaymentDate_Secu ,cod.PaymentDate_Cash)
            END,-1) AS LSCI_DateOfRecord
            ,cod.RoundingDesc


    FROM #EventsToDo ev 
    CROSS APPLY CA_Parse_CorpActnDtls_fn(MessageID) ent
    outer apply CA_Parse_CorpActnOptnDtls_fn(ev.MessageID) cod

you can see that I need the second function CA_Parse_CorpActnOptnDtls_fn(ev.MessageID) Because I want to compose an LSCI_DateOfRecord data using my user defined function. so is there any way to avoid the duplicate when I using the two functions together?

or is there any ways to build a temp list for the LSCI_DateOfRecord and RoundingDesc from the second function CA_Parse_CorpActnOptnDtls_fn(ev.MessageID) separately? And then I can update the table.

Any help is greatly appreciated.

Can you insert them to a temp table, remove the duplicate and insert to the real table? — DVT, Aug 15 '17 at 19:11
There are several ways to tackle this. But they all depend on what you mean by a duplicate. Is it just a duplicate key value or are entire rows being duplicated? Maybe a simple group by would solve the problem. Maybe something else. — Sean Lange, Aug 15 '17 at 19:16
Some sample data would go a long way here, as would the definition of those 2 UDF's which you have. This would help us understand what you define as a duplicate. Please show us what you're getting (as a select statement) and what you would expect to see — Eli, Aug 15 '17 at 19:20
The 2 UDF are a little bit long. But they are reading data from XML. So the Primary key for my table is EventID. Basically they are doing is parsing the corporate action event detail data to my table. — Rachel, Aug 15 '17 at 19:30
And how do i insert the data from the function to a temp table? i need to parsing the MessageID as a parameter for my function. And the MessageID is in the temp table #EventToDo — Rachel, Aug 15 '17 at 19:31
If those table valued function are a bit long I am worried they are not inline table but are instead the dreaded multi-statement table valued function. The performance of those is dreadful...usually worse than even scalar functions. But as I said previously we don't have enough information to provide the best solution here. Have you tried simply adding a distinct to your select query? — Sean Lange, Aug 15 '17 at 19:39

score 0 · Answer 1 · answered Aug 15 '17 at 20:43

Looking at the error and your SQL code, it's not APPLY operators causing the problem per se. It's the fact that one or both of the functions is returning more than a single row for set of EvenID & MessageID, and THAT is what's causing the PK violation.

Below is a simplified demonstration, using a string splitter function (DelimitedSplit8K)

IF OBJECT_ID('tempdb..#EventsToDo ', 'U') IS NOT NULL 
DROP TABLE #EventsToDo ;
GO

CREATE TABLE #EventsToDo (
    EventID BIGINT NOT NULL,
    MessageID BIGINT NOT NULL,
    MessageText VARCHAR(1000) NOT NULL 
    );
GO

INSERT #EventsToDo (EventID, MessageID, MessageText) VALUES
    (105818432, 37819929, 'Part 1,Part 2,Part 3,Part 4,Part 5');
GO

-----------------------------------------------------------------

-- create the AfterParse_CA_Events table with PRIMARY KEY (EvenID, MessageID)...
IF OBJECT_ID('tempdb..#AfterParse_CA_Events', 'U') IS NOT NULL 
DROP TABLE #AfterParse_CA_Events;
GO

CREATE TABLE #AfterParse_CA_Events (
    EvenID BIGINT NOT NULL,
    MessageID BIGINT NOT NULL,
    MessagePart VARCHAR(1000) NULL 
    PRIMARY KEY (EvenID, MessageID)
    );
GO

--===============================================================

-- see what happens when we try to insert the parsed message values
-- into AfterParse_CA_Events while it has a PK of (EvenID, MessageID)...
INSERT #AfterParse_CA_Events (EvenID, MessageID, MessagePart)
SELECT 
    etd.EventID, 
    etd.MessageID, 
    dsk.Item
FROM
    #EventsToDo etd
    CROSS APPLY dbo.DelimitedSplit8K(etd.MessageText, ',') dsk;
GO

--===============================================================
-- execute the code below in a separate execution
--===============================================================

-- now, let's modify the  AfterParse_CA_Events table so that we have "MessagePartID" 
-- and make that part of the PK
IF OBJECT_ID('tempdb..#AfterParse_CA_Events', 'U') IS NOT NULL 
DROP TABLE #AfterParse_CA_Events;
GO 

CREATE TABLE #AfterParse_CA_Events (
    EvenID BIGINT NOT NULL,
    MessageID BIGINT NOT NULL,
    MessagePartID INT NOT NULL,
    MessagePart VARCHAR(1000) NOT NULL 
    PRIMARY KEY (EvenID, MessageID, MessagePartID)
    );
GO

--===============================================================

-- Now let's try the insertion again...
INSERT #AfterParse_CA_Events (EvenID, MessageID, MessagePartID, MessagePart)
SELECT 
    etd.EventID, 
    etd.MessageID, 
    dsk.ItemNumber,
    dsk.Item
FROM
    #EventsToDo etd
    CROSS APPLY dbo.DelimitedSplit8K(etd.MessageText, ',') dsk;
GO

--===============================================================

-- check the inserted values...
SELECT 
    *
FROM
    #AfterParse_CA_Events apce;

HTH, Jason

Violation of PRIMARY KEY constraint when using cross apply and outer apply together

1 Answers1