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

Answer 3

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.

Answer 4

Here is a useful article by Michael J. Swart on the matter, which covers different patterns and antipatterns for implementing UPSERT in SQL Server:
https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/

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

Answer 5

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