How to upsert update or insert in SQL Server 2005

Question

I have a table in which I am inserting rows for employees, but the next time I want to enter a row, I only want to update with the necessary columns if it exits there; if not, I want to construct a new row.

How can we do this in SQL Server 2005?

My Query:

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

If it's first time then insert it into database else if exists update it. Can someone please help me with this?

Answer 1

Try to check for existence:

IF NOT EXISTS (SELECT * FROM dbo.Employee WHERE ID = @SomeID)

    INSERT INTO dbo.Employee(Col1, ..., ColN)
    VALUES(Val1, .., ValN)

ELSE

    UPDATE dbo.Employee
    SET Col1 = Val1, Col2 = Val2, ...., ColN = ValN
    WHERE ID = @SomeID

This is something that could be easily turned into a stored procedure and then called from the outside (for example, from a programming language like C# or whatever you're using).