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

Question

I have table in which I am inserting rows for 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 new row

How can we do this in 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 first time then insert it into database else if exists update it

how to do?

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 

You could easily wrap this into a stored procedure and just call that stored procedure from the outside (e.g. from a programming language like C# or whatever you're using).

Update: either you can just write this entire statement in one long string (doable - but not really very useful) - or you can wrap it into a stored procedure:

CREATE PROCEDURE dbo.InsertOrUpdateEmployee        @ID INT,        @Name VARCHAR(50),        @ItemName VARCHAR(50),          @ItemCatName VARCHAR(50),        @ItemQty DECIMAL(15,2) AS BEGIN     IF NOT EXISTS (SELECT * FROM dbo.Table1 WHERE ID = @ID)        INSERT INTO dbo.Table1(ID, Name, ItemName, ItemCatName, ItemQty)        VALUES(@ID, @Name, @ItemName, @ItemCatName, @ItemQty)     ELSE        UPDATE dbo.Table1        SET Name = @Name,            ItemName = @ItemName,            ItemCatName = @ItemCatName,            ItemQty = @ItemQty        WHERE ID = @ID END 

and then just call that stored procedure from your ADO.NET code

Answer 2

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 update fails, the new row will be inserted