How to upsert (update or insert) in SQL Server 2005

Question

I have a table in which I am inserting the rows for the employee but next time when I want to insert row I don't want to insert again data for that employee just want to update with required columns if it exits there if not then create a new row

How can we do this in the SQL Server 2005?

I am using JSP.

My query is 

String sql="insert into table1(id,name,itemname,itemcatName,itemQty)values('val1','val2','val3','val4','val5')";

If it's the first time then insert it into database else if exists update it

How to do?

Answer 1

You can use @@ROWCOUNT to check whether row should be inserted or updated:

update table1 
set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5'
where id = 'val1'
if @@ROWCOUNT = 0
insert into table1(id, name, itemname, itemcatName, itemQty)
values('val1', 'val2', 'val3', 'val4', 'val5')

In this case if the update gets failed, the new row will be inserted.

Interested in SQL ? Check out Microsoft SQL Server Database certifications by Intellipaat.

For more information visit :