Solutions For Insert or Update on SQL Server

Solutions for INSERT OR UPDATE on SQL Server

don't forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous.

When multiple threads will try to perform Insert-or-update you can easily
get primary key violation.

Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.

To avoid deadlocks and PK violations you can use something like this:

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

or

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

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.

MSSQL INSERT OR UPDATE if EXISTS

You can use 2 statements (INSERT + UPDATE) in the following order. The update won't update anything if it doesn't exist, the insert won't insert if it exist:

UPDATE T SET
    name = 'A',
    age = 19
FROM
    [table] AS T
WHERE
    T.id = 1

INSERT INTO [table] (
    id,
    name,
    age)
SELECT
    id = 1,
    name = 'A',
    age = 19
WHERE
    NOT EXISTS (SELECT 'not yet loaded' FROM [table] AS T WHERE T.id = 1)

Or a MERGE:

;WITH ValuesToMerge AS
(
    SELECT
        id = 1,
        name = 'A',
        age = 19
)
MERGE INTO 
    [table] AS T
USING
    ValuesToMerge AS V ON (T.id = V.id)
WHEN NOT MATCHED BY TARGET THEN
    INSERT (
        id,
        name,
        age)
    VALUES (
        V.id,
        V.name,
        V.age)
WHEN MATCHED THEN
    UPDATE SET
        name = V.name,
        age = V.name;

Safe solutions for INSERT OR UPDATE on SQL Server 2016

MERGE statement can perform both UPDATE and INSERT (and DELETE if needed).

Even though it is a single atomic statement, it is important to use HOLDLOCK query hint to prevent race condition. There is a blog post “UPSERT” Race Condition With MERGE by Dan Guzman where he explains in great details how it works and provides a test script to verify it.

The query itself is straight-forward:

DECLARE @NewKey NVARCHAR(MAX) = ...;

MERGE INTO dbo.MyTable WITH (HOLDLOCK) AS Dst
USING 
(
    SELECT @NewKey AS NewKey
) AS Src
ON Src.NewKey = Dst.[Key]
WHEN MATCHED THEN
UPDATE
SET NumberOfInserts = NumberOfInserts + 1
WHEN NOT MATCHED THEN
INSERT
(
    [Key]
    ,NumberOfInserts
)
VALUES
(
    @NewKey
    ,0
);

---

Of course, you can also use explicit two-step approach with a separate check if a row exists and separate UPDATE and INSERT statements. Just make sure to wrap them all in a transaction with appropriate table locking hints.

See Conditional INSERT/UPDATE Race Condition by Dan Guzman for details.

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

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

Best strategy for Massive Insert/Update using jdbc in mssqlserver

After looking into it, the best solution i found for the following,

First as stated in the comments, I read the whole file loaded it into memory in a java structure.

After loading the file I then iterated the java stricter and started to load each record in the batch. At the same time I kept a counter on each item I add the batch.
When the counter hits 5000, I do a commit on the batch, reset the counter to 0 and keep adding to the following items I either hit 5000 again or reached the end of the iteration.

By doing this I am preventing MSSQL from creating a lock on the table, and the table can still be used by the other processes and applications.

ADO Update Or Insert is missing Output Inserted Value for second Execute Branch

Try storing the output to a table variable and then return the variable.

DECLARE @Output TABLE (IndexField BIGINT);

UPDATE Artikel SET [SuchBeg] = 'TEST', [ArtNr] = '19904.S' OUTPUT INSERTED.[IndexField] INTO @Output WHERE [ArtNr] = '19904.S'
if @@ROWCOUNT = 0
INSERT INTO Artikel ([SuchBeg], [ArtNr]) Output Inserted.[IndexField] INTO @Output VALUES ('TEST', '19904.S');

SELECT * FROM @Output;

---

Related Topics