Click here to Skip to main content
15,940,550 members
Please Sign up or sign in to vote.
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
Updated 2-Oct-12 23:16pm
v2

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
 
Share this answer
 
Comments
Sandeep Mewara 4-Oct-12 16:35pm    
My 5! losmac :)
Maciej Los 4-Oct-12 16:37pm    
Thank you, Sandeep ;)
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
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900