how to avoid inserting duplicate key to the table?

For example, the EMPLOYEE TABLE has field EMPID (primary key) and NAME

If I execute the following sql statement one by one, I will get
error "violation of primary key contraint. cannot insert duplicate
key in table employee."

INSERT INTO EMPLOYEE (EMPID, NAME) VALUES ('1001','Joe');
INSERT INTO EMPLOYEE (EMPID, NAME) VALUES ('1001','Sue');


In my case, should I check if the key value already exists in the
table, then update the record instead of inserting the record?

please advise. thanks!!

Hi

CREATE TABLE #Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 CHAR(1) NOT NULL
)
GO
INSERT INTO #Test VALUES (1001,'A')
GO

DECLARE @par INT
SET @par=1001
IF NOT EXISTS (SELECT * FROM #Test WHERE col1=@par)
INSERT INTO .....Here
ELSE
PRINT 'You are going to insert a duplicate value'







<jrefact***@hotmail.com> wrote in message
Show quoteHide quote

I suggest you make the EMPID Column as Identity.

Kiran

<jrefact***@hotmail.com> wrote in message
Show quoteHide quote

How you approach this situation depends on why the new record with an
existing EMPID is being inserted in the first place. Is Sue an entirely new
employee who will be Joe's co-worker, or did Joe have a sex change and you
need to update his name to Sue? If you are just inserting records and need
to insure that EMPID is unique, then EMPID needs to be an identity column so
SQL Server automatically increments the value. If you need to perform an
operation where a record is updated if exists or inserted if new then do
something like the following:

update ... where empid = 1001
if @@rowcount = 0
begin
   insert ...
end

<jrefact***@hotmail.com> wrote in message
Show quoteHide quote

Can I do this?
is there a set-based solution to this task?
Select Query
Combining Two Tables ?
xp_cmdshell by proxy account is not working, please help