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 开发者_如何学JAVAerror 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