CODEWITHSUNDEEP
sql-serversql-server-2005
Sheik Syed Mohaideen
Sheik Syed Mohaideen

Reputation: 191

Check for duplicate record before insert in SQL Server 2005

In sql server 2005 I have a table like this

empno             name          project
--------       --------       -----------
1               ram              pro-1
2               sheik            pro-2

Now I want to raise error when I try to insert record with empno and project values already present in table.

Upvotes: 0

Views: 3915

Answers (2)

Elias Hossain
Elias Hossain

Reputation: 4469

If exists then RAISERROR unless insert accordingly, code block is given below:

IF EXISTS (SELECT 1 FROM targetTable AS t
     WHERE t.empNo = @yourEmpNo
         AND t.project = @yourProject)
BEGIN
   RAISERROR('Raising errors',18,1)
END
ELSE
BEGIN
   INSERT INTO yourTable (empno, name, project)
      SELECT @empno, @name, @project
END

Upvotes: 3

Adam Wenger
Adam Wenger

Reputation: 17540

If you want to raise an error, you could put a UNIQUE INDEX or UNIQUE CONSTRAINT on (empno, project) When you try to INSERT a value that conflicts with the INDEX/CONSTRAINT an exception will be thrown.

Alternatively you can insert if the record doesn't exist or update name if it is already in the table:

IF NOT EXISTS (SELECT 1
             FROM yourTable AS t
             WHERE t.empNo = @yourEmpNo
                AND t.project = @yourProject)
BEGIN
   INSERT INTO yourTable (empno, name, project)
   SELECT @empno, @name, @project
END
ELSE
BEGIN
   UPDATE t
   SET t.Name = @name
   FROM yourTable AS t
   WHERE t.empno = @empno
      AND t.project = @project
END

Upvotes: 3

Related Questions