当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - violation of primary key ???? even when there is a chk not to insert the record

问题描述:

INSERT INTO table 2

SELECT values FROM table 1 LEFT JOIN table 2

ON 1.col1 = 2.col1 and

1.col2 = 2.col2 and

1.col3 = 2.col3

WHERE

(

2.col1 IS NULL AND

2.col2 IS NULL AND

2.col3 IS NULL

)

col1,col2,col3 primary keys for table 2

Also did a inner join on condition primary key <>

added a if not exists ( select 1 from table 2 join table 1 on primary keys = )

always get violation of primary key error cannot insert

please help

网友答案:

Without trying to figure out why you're getting the problem you're seeing, I suggest you re-write your SQL to use the MERGE command. See here.

It allows you to specify what to do if there is a duplicate record or not.

As a note, if you wish to ensure a stored procedure runs exclusively (you hinted at this), you need the following:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE            -- most restrictive

        BEGIN TRY
            BEGIN TRAN

            DECLARE @LockResult int

            SET @LockName = 'MyLockName'

            EXEC    @LockResult = sp_getapplock @Resource = @LockName, @LockMode = 'Exclusive', @LockTimeout = 0

            if @LockResult <> 0 begin
                SET @Failed = 1
            end else begin
                -- WORK HERE
            end

            -- FINALIZE TRANSACTION
        END TRY
        BEGIN CATCH
            -- CATCH CODE
        END CATCH

        SET TRANSACTION ISOLATION LEVEL READ COMMITTED  -- set back to default
分享给朋友:
您可能感兴趣的文章:
随机阅读: