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

User · Answer

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

User · Answer

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

User · Answer

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    check if row exists 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

User · Answer

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

User · Answer

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

[sql-server-2005] How to upsert (update or insert) in SQL Server 2005

Examples related to sql-server-2005