SQL插入避免重复最佳实践 [英] Sql Insert Avoid Duplicate Best Practice

问题描述

我对sql命令的最佳实践感到不满.

我需要在这样的表中插入行

idA，idB
1 2
2 3
2 4
1 1

idA + idB键

我显然需要在添加之前检查行是否存在，以避免System.Data.SqlClient.SqlException(0x80131904):违反PRIMARY KEY约束


我的选择是:

1)使用检查存在性方法，在表中选择符号插入命令之前，在表中选择idA = XXX和idB = yyy的行

2)使用有点复杂的插入命令，该命令会自动执行
就像

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

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

推荐答案

避免重复的最佳实践是编写

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

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

另一个选项:

Another option:

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

这篇关于SQL插入避免重复最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！