Stop Inserting in Table if record already exists

Question

I have sql server procedure, please see below.

ALTER PROCEDURE [dbo].[uspInsertDelegate]
(
    @CourseID int,
    @CPUserID int,
    @StatusID int,
    @CreateUser varchar(25)

)
AS
    SET NOCOUNT OFF;
INSERT INTO tblDelegate                      
(
    CourseID, 
    CPUserID, 
    StatusID, 
    CreateUser 

)
VALUES     
(
    @CourseID,
    @CPUserID,
    @StatusID,
    @CreateUser
)

RETURN

Now I don't want to insert into table tblDelegate if the inserting courseid and cpuserid is same for that records in table tblDelegate

are you trying to upsert? http://stackoverflow.com/questions/13540/insert-update-stored-proc-on-sql-server — Sam Saffron, Sep 07 '09 at 08:44

pjp · Answer 1 · 2009-09-07T08:51:32.967

Add a unique key constraint to the courseid and cpuuserid columns.

You'll then get a key violation if you try to insert a dupe.

As well as doing this you can test to see if the value exists before inserting it using your stored procedure.

BEGIN TRAN

SELECT 1 
FROM tblDelegate WITH (TABLOCK) 
WHERE CourseId=@CourseID 
      AND CPUserID=@CPUserId
IF @@rowcount = 0
BEGIN
--Record doesn't already exist
--Insert it
END

COMMIT

score 2 · Accepted Answer · answered Sep 07 '09 at 08:40

2

Simply test first. In SQL Server 2005 you could also TRY/CATCH to ignore a duplicate error.

IF NOT EXISTS (SELECT *
        FROM tblDelegate
        WHERE CourseID = @CourseID etc)
    INSERT INTO tblDelegate                      
    (
        CourseID, 
        CPUserID, 
        StatusID, 
        CreateUser 

    )
    VALUES     
    (
        @CourseID,
        @CPUserID,
        @StatusID,
        @CreateUser
    )

May I ask: do you mean "SET NOCOUNT ON"?

answered Sep 07 '09 at 08:40

gbn

422,506
82
585
676

1

this is not safe for concurrency, with heavy concurrency you will get dupes. – Sam Saffron Sep 07 '09 at 08:43
Always worth adding the unique key constraint too and using an appropriate locking level. – pjp Sep 07 '09 at 08:53
@Sam: True, seen it happen, but it's OK for a lot of situations. @pjp: The PK would normally be the unique constraint – gbn Sep 07 '09 at 10:13

no_one · Answer 3 · 2009-09-07T09:18:42.103

1

What version of SQL Server you are using ? If you are on 2008 look up the MERGE statement.

Use the IF NOT Exists Clause then as pointed in the first answer.

edited Sep 07 '09 at 09:18

answered Sep 07 '09 at 08:42

no_one

1,852
12
11

Stop Inserting in Table if record already exists

3 Answers3

Linked