Sql Insert Avoid Duplicate Best Practice

Question

0.00/5 (No votes)

See more:

i'm dubtfull about the best practice on sql command.

my need is to insert rows in a table like that

idA, idB
1 2
2 3
2 4
1 1

idA+idB keys

i need obviously to check if the row exists before adding in order to avoid System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint

My option are :

1) use a check existance method that select in the table the row with idA = XXX and idB = yyy , before a symple insert command

2) use a bit complex insert command that do that automatically
like

SQL

INSERT 
INTO    MYTABLE
        ([IdA],[IdB])
SELECT  TOP 1 @IdA,@IdB
FROM    MYTABLE
WHERE   NOT EXISTS
        (
            SELECT  MYTABLE.IdA
            FROM    MYTABLE
            WHERE   MYTABLE.IdA = @IdA
            AND     MYTABLE.IdB= @IdB
        )

is there any other option ?

what do you think is the best ?

thanks

Posted 2-Oct-12 23:15pm

nrgjack

Updated 2-Oct-12 23:16pm

v2

Add a Solution

2 solutions

Solution 1

Another option:

SQL

INSERT 
INTO    MYTABLE
        ([IdA],[IdB])
SELECT  TOP 1 @IdA,@IdB
FROM    MYTABLE T1
LEFT OUTER JOIN 
(
    SELECT  MYTABLE.IdA
    FROM    MYTABLE
    WHERE   MYTABLE.IdA = @IdA
    AND     MYTABLE.IdB= @IdB
    
)AS T2
ON T1.Ida = T2.Ida
WHERE T2.Ida IS NULL

Posted 2-Oct-12 23:36pm

Kuthuparakkal

Comments

nrgjack 3-Oct-12 5:41am

yep it is a good option too,
it is more or less like the not-exists also considering performances isn't it?

personally i think the INSERT WHERE NOT EXISTS is a little more readable

Kuthuparakkal 3-Oct-12 5:58am

Agree. Conclude it as :

If you need to find rows that don’t have a match in a second table, and the columns are nullable, use NOT EXISTS. If you need to find rows that don’t have a match in a second table, and the columns are not nullable, use NOT EXISTS or NOT IN.

The LEFT OUTER JOIN … IS NULL method is slower when the columns are indexed and it’s perhaps not as clear what’s happening. It’s reasonably clear what a NOT EXISTS predicate does, with LEFT OUTER JOIN it’s not immediately clear that it’s a check for non-matching rows, especially if there are several where clause predicates.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2012-10-04T10:07:00

The best practice to avoid duplicates is to write stored procedure[^] ;)

SQL

CREATE PROCEDURE ...
    @ida int = 0,
    @idb int =0
AS
BEGIN
    --declare variables to get the values of [IdA] and [IdB]
    DECLARE @mda int
    DECLARE @dmb int

    --initialize variables
    SELECT @mda = ISNULL([IdA],0), @mdb = ISNULL([IdB],0)
    FROM MYTABLE
    WHERE  [IdA] = @ida AND [IdB]= @idb

    --insert data if one of id's doesn't exists in the table
    IF @mda=0 OR @mdb=0 
    BEGIN
         INSERT INTO MYTABLE ([IdA],[IdB])
         VALUES(@ida, @idb)
    END

    --if you would like to know how many records were affected, uncomment below line
    --RETURN @@ROWCOUNT

END