CODEWITHSUNDEEP
sql-servert-sql
Madhu
Madhu

Reputation: 39

Which approach is good to insert a record IF NOT EXISTS VS @@ROWCOUNT = 0

I need to insert a record into table if the record not exists. I have below table

CREATE TABLE #EMP 
(
    EmpID INT IDENTITY(1, 1),
    EmpName VARCHAR(100),
    Designation VARCHAR(10)
)

INSERT INTO #EMP (EmpName, Designation)
VALUES ('Ramesh', 'Teamlead'), 
       ('Suresh', 'Manager')

Now I want to insert a record by checking the record exists or not

I can one of these two approaches:

Approach #1:

DECLARE @EmpID INT

SELECT @EmpID = EmpID
FROM #EMP 
WHERE EmpName = 'Ramesh' AND Designation = 'Teamlead'

IF @@ROWCOUNT = 0
    INSERT INTO #EMP (EmpName, Designation)
    VALUES ('Ramesh', 'Teamlead')

Approach #2:

IF NOT EXISTS (SELECT NULL 
               FROM #EMP 
               WHERE EmpName = 'Ramesh' AND Designation = 'Teamlead')
BEGIN
    INSERT INTO #EMP (EmpName, Designation)
    VALUES ('Ramesh', 'Teamlead')
END

Can anyone suggest which one is the better of the two?

Thank you

Upvotes: 0

Views: 145

Answers (1)

Thom A
Thom A

Reputation: 95589

Your second approach is flawed, you check for 'Ramesh' but if they don't exist also INSERT 'Suresh', who might already exist.

I would personally suggest doing it all in one statement. Something like this:

INSERT INTO #EMP (EmpName, Designation)
SELECT EmpName,
       Designation
FROM (VALUES('Ramesh','Teamlead'),
            ('Suresh','Manager'))V(EmpName, Designation)
WHERE NOT EXISTS (SELECT 1
                  FROM #EMP E
                  WHERE E.EmpName = V.EmpName
                    AND E.Designation = V.Designation);

As mentioned by @DanGuzman, this does pose a problem (as do yours) that it could suffer race conditions (though with a local temporary table this would not be an issue). One method would be to therefore change the isolation level to serializable prior to the statement:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Alternatively, you could use the hint HOLDLOCK on the table in the NOT EXISTS, which would be by using WITH (HOLDLOCK).

Upvotes: 2

Related Questions