Unique constraint on multiple rows..how just not insert duplicate without stopping all

Question

I have a table that I'm trying to add the following constraint to, to avoid duplicates when inserting a duplicate of the constraint columns, but I'm not sure how to get it to not fail the insert entirely on just that duplicate insert attempt, if there are multiple rows being inserted.

This is the constraint I'm adding:

BEGIN
IF NOT EXISTS (SELECT * FROM sys.tables where name = N'T_1321_PNAnnotationCommitReport')
        BEGIN
            CREATE TABLE AnnotationCommitReport (
                 [id] [INT] IDENTITY(1,1) PRIMARY KEY not null,  --key
                 [progressnote_id] [INT] NOT NULL,
                 [form_id] [INT] NOT NULL,
                 [question_id] [INT],
                 [question_value] [VARCHAR](max),
                 [associatedconcept_id] [INT],
                 [OI_create_date] [DATETIME], --SCHED_RPT_DATE
                 [crte_date] [DATETIME] DEFAULT CURRENT_TIMESTAMP,
            );
            --create unique constraint on indicated columns to prevent dups inserted
            ALTER TABLE AnnotationCommitReport
            ADD CONSTRAINT PN_Unique UNIQUE (progressnote_id, form_id, question_id, question_value, associatedconcept_id, OI_create_date)
        END

This is where I add the rows to it that could be duplicated:

INSERT INTO AnnotationCommitReport(progressnote_id,form_id,question_id,question_value,associatedconcept_id, OI_create_date, crte_date)
        SELECT  progressnote_id,
                form_id,
                question_id,
                questionvalue,
                concept_id,
                create_date as OI_create_date,
                getdate()
        FROM FORM_QUESTION 
        WHERE
            (create_date > @LAST_RUN_DATE
            AND
            create_date <= @RUN_TO_DATE)
END --it's much more complicated than this, so I simplified for this example

How do I make it just not insert that duplicate row? Someone suggested try/catch, but I think it would keep the remainder from being inserted if there are rows to insert after the duplicate row. I found postgresql - avoid duplicate inserts without unique constraint, but I'm not sure if it can be used, and I'm also not sure what they are talking about with a map and how to apply it to what I have.

Update: Here's more detail for that last Insert than I Initially provided:

INSERT INTO dbo.AnnotationCommitReport(progressnote_id,form_id,question_id,question_value,associatedconcept_id, OI_create_date, crte_date) 
        SELECT  progressnote_id,
                a.form_id,
                question_id,
                questionvalue,
                fq.concept_id,
                a.create_date as OI_create_date,
                getdate()
        FROM (
            SELECT  form_id,
                    progressnote_id,
                    R.Q.value('@id', 'varchar(max)') AS questionid,
                    R.Q.value('@value', 'varchar(max)') AS questionvalue,
                    t.create_date
            FROM
                    @tableNotes t
            OUTER APPLY t.form_questions.nodes('/RESULT/QUESTIONS/QUESTION') AS R(Q)
            WHERE 
                    ISNUMERIC(R.Q.value('@id', 'varchar(max)')) <> 0
            ) a
        INNER JOIN [CKOLTP_DEV]..FORM_QUESTION fq ON
                   fq.form_id = a.form_id AND
                   fq.question_id  = a.questionid
        
        WHERE
            (a.create_date > @LAST_RUN_DATE
            AND
            a.create_date <= @RUN_TO_DATE)

When performing a DML operation if one row in the operation fails the *entire* operation is rolled back, not just the offending row. If you need to have each row inserted/updated/etc evaluated and error separately then you also need to resolve each of those rows separately; that comes with (severe) performance impacts though. Therefore you would likely be better off using an `EXISTS` to compare the 2 datasets (the table and the new data) and check if the unique value does indeed *not* exist in the table, and then do *something* else with the rows that do. — Thom A, Oct 26 '20 at 16:21
@Michele I saw where you edited your question to add the tag plsql, because this i s about stored procedures. Please remove that tag. PL/SQL stands for Procedural Language/Structured Query Language. It is Oracle's proprietary language for processing an Oracle database. It is vastly different that MS T-SQL. Most of what you have posted is invalid for plsql. — Belayer, Oct 29 '20 at 02:47

score 1 · Accepted Answer · answered Oct 26 '20 at 16:32

1

Why not write your insert as select statement in such a way as to filter out duplicates using the where condition.

There's an example of this approach (though obviously not the exact same problem), but see this: Avoid duplicates in INSERT INTO SELECT query in SQL Server

In your case it would involve doing a join between AnnotationCommitReport and the insert as select, to avoid the duplicates.

answered Oct 26 '20 at 16:32

Menelaos

23,508
18
90
155

Can you give an example? My insert into/select/from is much more complicated than I show. – Michele Oct 26 '20 at 17:01
I added more detail as to my actual insert in the question. That would be really helpful if you give more info on how to do what you are suggesting. – Michele Oct 26 '20 at 17:19
I'm going to add what I wound up doing below – Michele Oct 26 '20 at 18:15

score 0 · Answer 2 · answered Oct 26 '20 at 16:27

0

How do I make it just not insert that duplicate row?

Use MERGE instead of INSERT so you can check for duplicates and only insert when the new row is NOT MATCHED at the target, or set the IGNORE_DUP_KEY option on the unique index.

answered Oct 26 '20 at 16:27

David Browne - Microsoft

80,331
6
39
67

But it's not the key that would indicate duplicates, it would be multiple column values. Do you still think merge is a good idea? – Michele Oct 26 '20 at 16:45
A table can have multiple keys. – David Browne - Microsoft Oct 26 '20 at 16:53
I'd appreciate if you give more info on how I'd do that – Michele Oct 26 '20 at 17:31

Michele · Answer 3 · 2021-01-27T18:20:22.600

This is what I wound up doing, following @Menios' suggestion

--I created a temp table with what I intend to put in DB table
INSERT INTO @pnAnnotationCommitReport_ToBeInserted(progressnote_id,form_id,question_id,question_value,associatedconcept_id, OI_create_date, crte_date) 
        SELECT  progressnote_id,
                a.form_id,
                question_id,
                questionvalue,
                fq.concept_id,
                a.create_date as OI_create_date,
                getdate()
        FROM (
            SELECT  form_id,
                    progressnote_id,
                    R.Q.value('@id', 'varchar(max)') AS questionid,
                    R.Q.value('@value', 'varchar(max)') AS questionvalue,
                    t.create_date
            FROM
                    @tableNotes t
            OUTER APPLY t.form_questions.nodes('/RESULT/QUESTIONS/QUESTION') AS R(Q)
            WHERE 
                    ISNUMERIC(R.Q.value('@id', 'varchar(max)')) <> 0
            --AND 
            --      create_date > @LAST_RUN_DATE
            ) a
        INNER JOIN [CKOLTP_DEV]..FORM_QUESTION fq ON
                   fq.form_id = a.form_id AND
                   fq.question_id  = a.questionid
        
        WHERE
            (a.create_date > @LAST_RUN_DATE
            AND
            a.create_date <= @RUN_TO_DATE)
            --AND NOT EXISTS(SELECT progressnote_id, a.form_id, question_id, a.questionvalue, fq.concept_id, a.create_date where pn.progressnote_id=a.progressnote_id, )

--Insert into main table, only values that aren't duplicated
INSERT INTO AnnotationCommitReport(progressnote_id,form_id,question_id,question_value,associatedconcept_id, OI_create_date, crte_date) 
        SELECT 
          pni.progressnote_id
        , pni.form_id
        , pni.question_id
        , pni.question_value
        , pni.associatedconcept_id
        , pni.OI_create_date
         FROM @pnAnnotationCommitReport_ToBeInserted pni
         WHERE NOT EXISTS 
            (SELECT progressnote_id,form_id,question_id,question_value,associatedconcept_id, OI_create_date, crte_date 
             FROM AnnotationCommitReport pn
             WHERE 
             pni.progressnote_id=pn.progressnote_id
             AND
             pni.form_id=pn.form_id
             AND
             pni.question_id=pn.question_id
             AND
             pni.question_value=pn.question_value
             AND
             pni.associatedconcept_id=pn.associatedconcept_id
             AND
             pni.OI_create_date=pn.OI_create_date)

Unique constraint on multiple rows..how just not insert duplicate without stopping all

3 Answers3