Update If Exists Else Insert in SQL Server 2008

Microsoft SQL Server - best way to 'Update if exists, or Insert'

Every one of them has different purpose, pros and cons.

Option 1 is good for multi row inserts/updates. However It only checks primary key constraints.

Option 2 is good for small sets of data. Single record insertion/update. It is more like script.

Option 3 is best for big queries. Lets say, reading from one table and inserting/updating to another accordingly. You can define which condition to be satisfied for insertion and/or update. You are not limited to primary key/unique constraint.

SQL Update if exists and older, else insert

merge @DataTable as dt
using (select @data as data, @event as event, @name as name, @timestamp as timestamp) source 
on dt.name=source.name and dt.event=source.event
when matched and dt.timestamp <= source.timestamp then
  update
  set timestamp = source.timestamp, data = source.data
when matched and dt.timestamp > source.timestamp then 
  insert(data, event, name, timestamp)
  values(data, event, name, timestamp)
when not matched then
  insert(data, event, name, timestamp)
  values(data, event, name, timestamp)

UPDATE if exists else INSERT in SQL

The below query will fulfill your requirement.

INSERT INTO `ALLOWANCE` (`EmployeeID`, `Year`, `Month`, `OverTime`,`Medical`,
`Lunch`, `Bonus`, `Allowance`) values (10000001, 2014, 4, 10.00, 10.00,
10.45, 10.10, 40.55) ON DUPLICATE KEY UPDATE `EmployeeID` = 10000001

How to execute IF EXISTS UPDATE ELSE INSERT syntax in SSMS 2008

You can simply write this query.

 UPDATE 
       O 
    SET                           
       O.Date_Added = R.Date_Added,
       O.Real_Exfact = R.Real_Exfact,
       O.Excess_Top_Up = R.Excess_Top_Up 
   FROM    
       SCM_Top_Up_Operational O JOIN SCM_Top_Up_Rolling R ON O.String = R.String
   WHERE   
       O.String = R.string
       AND R.Date_Added > O.Date_Added;

   INSERT INTO SCM_Top_Up_Operational 
            (String,Date_Added,Real_Exfact,Article_ID,Excess_Top_Up,Plant) 
         SELECT 
             R.String,
             R.Date_Added,R.Real_Exfact,R.Article_ID,R.Excess_Top_Up,R.Plant 
         FROM 
             SCM_Top_Up_Rolling R 
         WHERE 
             R.String NOT IN (SELECT String FROM SCM_Top_Up_Operational)

Danger of using 'IF EXISTS... UPDATE .. ELSE .. INSERT' and what is the alternative?

Use MERGE

Your SQL fails because 2 concurrent overlapping and very close calls will both get "false" from the EXISTS before the INSERT happens. So they both try to INSERT, and of course one fails.

This is explained more here: Select / Insert version of an Upsert: is there a design pattern for high concurrency? THis answer is old though and applies before MERGE was added

SQL Server how to UPDATE if exists else INSERT by Python

Change the batch to have local variables for each parameter, eg:

sql_insert = '''
    declare @product_id int = ?
    declare @product_name varchar(100) = ?
    declare @price decimal(10,2) = ?
        
    UPDATE [atetestdb].[dbo].[products] 
    SET product_name = @product_name, price = @price
    WHERE product_id = @product_id

    IF @@ROWCOUNT = 0
        INSERT INTO [atetestdb].[dbo].[products] 
            (product_id, product_name, price)
        VALUES (@product_id, @product_name, @price)
'''

---

Related Topics