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

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?

5 Answers

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

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

You can check if the row exists, and then INSERT or UPDATE, but this guarantees you will be performing two SQL operations instead of one:

1. check if row exists
2. insert or update row

A better solution is to always UPDATE first, and if no rows were updated, then do an INSERT, like so:

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') 

This will either take one SQL operations, or two SQL operations, depending on whether the row already exists.

But if performance is really an issue, then you need to figure out if the operations are more likely to be INSERT's or UPDATE's. If UPDATE's are more common, do the above. If INSERT's are more common, you can do that in reverse, but you have to add error handling.

BEGIN TRY insert into table1(id, name, itemname, itemcatName, itemQty) values('val1', 'val2', 'val3', 'val4', 'val5') END TRY BEGIN CATCH update table1 set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5' where id = 'val1' END CATCH 

To be really certain if you need to do an UPDATE or INSERT, you have to do two operations within a single TRANSACTION. Theoretically, right after the first UPDATE or INSERT (or even the EXISTS check), but before the next INSERT/UPDATE statement, the database could have changed, causing the second statement to fail anyway. This is exceedingly rare, and the overhead for transactions may not be worth it.

Alternately, you can use a single SQL operation called MERGE to perform either an INSERT or an UPDATE, but that's also probably overkill for this one-row operation.

Consider reading about SQL transaction statements, race conditions, SQL MERGE statement.

Here is a useful article by Michael J. Swart on the matter, which covers different patterns and antipatterns for implementing UPSERT in SQL Server:

It addresses associated concurrency issues (primary key violations, deadlocks) - all of the answers provided here yet are considered antipatterns in the article (except for the @Bridge solution using triggers, which is not covered there).

Here is an extract from the article with the solution preferred by the author:

Inside a serializable transaction with lock hints:

CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) ) AS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRAN IF EXISTS ( SELECT * FROM dbo.AccountDetails WITH (UPDLOCK) WHERE Email = @Email ) UPDATE dbo.AccountDetails SET Etc = @Etc WHERE Email = @Email; ELSE INSERT dbo.AccountDetails ( Email, Etc ) VALUES ( @Email, @Etc ); COMMIT 

There is also related question with answers here on stackoverflow: Insert Update stored proc on SQL Server

You could do this with an INSTEAD OF INSERT trigger on the table, that checks for the existance of the row and then updates/inserts depending on whether it exists already. There is an example of how to do this for SQL Server 2000+ on MSDN here:

CREATE TRIGGER IO_Trig_INS_Employee ON Employee INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON -- Check for duplicate Person. If no duplicate, do an insert. IF (NOT EXISTS (SELECT P.SSN FROM Person P, inserted I WHERE P.SSN = I.SSN)) INSERT INTO Person SELECT SSN,Name,Address,Birthdate FROM inserted ELSE -- Log attempt to insert duplicate Person row in PersonDuplicates table. INSERT INTO PersonDuplicates SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE() FROM inserted -- Check for duplicate Employee. If no duplicate, do an insert. IF (NOT EXISTS (SELECT E.SSN FROM EmployeeTable E, inserted WHERE E.SSN = inserted.SSN)) INSERT INTO EmployeeTable SELECT EmployeeID,SSN, Department, Salary FROM inserted ELSE --If duplicate, change to UPDATE so that there will not --be a duplicate key violation error. UPDATE EmployeeTable SET EmployeeID = I.EmployeeID, Department = I.Department, Salary = I.Salary FROM EmployeeTable E, inserted I WHERE E.SSN = I.SSN END