Bits of .NET

Inserting if row doesn’t exist, updating if it does? MERGE statement does both in one atomic operation without race conditions.

The Problem – Separate Insert/Update:

-- Check if exists
IF EXISTS (SELECT 1 FROM Products WHERE ProductId = @Id)
BEGIN
    -- Update
    UPDATE Products 
    SET ProductName = @Name, Price = @Price
    WHERE ProductId = @Id;
END
ELSE
BEGIN
    -- Insert
    INSERT INTO Products (ProductId, ProductName, Price)
    VALUES (@Id, @Name, @Price);
END

-- Problems:
-- - Race condition (two requests at same time)
-- - Two round trips to database
-- - Not atomic

The MERGE Solution:

MERGE INTO Products AS target
USING (SELECT @Id AS ProductId, @Name AS ProductName, @Price AS Price) AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN
    UPDATE SET 
        ProductName = source.ProductName,
        Price = source.Price
WHEN NOT MATCHED THEN
    INSERT (ProductId, ProductName, Price)
    VALUES (source.ProductId, source.ProductName, source.Price);

-- Atomic operation: Insert OR Update in single statement
-- No race conditions

Bulk Upsert from Temp Table:

-- Load new data into temp table
CREATE TABLE #NewProducts (
    ProductId INT,
    ProductName NVARCHAR(100),
    Price DECIMAL(10,2)
);

INSERT INTO #NewProducts VALUES
(1, 'Laptop', 1200.00),
(2, 'Mouse', 25.00),
(3, 'Keyboard', 75.00);

-- Merge all at once
MERGE INTO Products AS target
USING #NewProducts AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN
    UPDATE SET 
        ProductName = source.ProductName,
        Price = source.Price,
        UpdatedDate = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (ProductId, ProductName, Price, CreatedDate)
    VALUES (source.ProductId, source.ProductName, source.Price, GETDATE());

-- Updates existing products, inserts new ones
-- All in single statement

With DELETE (Synchronize Tables):

-- Make target table exactly match source
MERGE INTO Products AS target
USING #NewProducts AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN
    UPDATE SET 
        ProductName = source.ProductName,
        Price = source.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductId, ProductName, Price)
    VALUES (source.ProductId, source.ProductName, source.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- Inserts new products
-- Updates existing products
-- Deletes products not in source
-- = Perfect synchronization

Get Modified Rows Output:

MERGE INTO Products AS target
USING #NewProducts AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN UPDATE SET Price = source.Price
WHEN NOT MATCHED THEN INSERT VALUES (source.ProductId, source.ProductName, source.Price)
OUTPUT 
    $action AS Action,
    INSERTED.ProductId,
    INSERTED.ProductName,
    DELETED.Price AS OldPrice,
    INSERTED.Price AS NewPrice;

-- Returns:
-- Action | ProductId | ProductName | OldPrice | NewPrice
-- UPDATE | 1         | Laptop      | 1000.00  | 1200.00
-- INSERT | 3         | Keyboard    | NULL     | 75.00

Conditional Logic:

-- Only update if source price is different
MERGE INTO Products AS target
USING #NewProducts AS source
ON target.ProductId = source.ProductId
WHEN MATCHED AND target.Price <> source.Price THEN
    UPDATE SET Price = source.Price
WHEN NOT MATCHED THEN
    INSERT (ProductId, ProductName, Price)
    VALUES (source.ProductId, source.ProductName, source.Price);

-- Only insert if price > 0
MERGE INTO Products AS target
USING #NewProducts AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN UPDATE SET Price = source.Price
WHEN NOT MATCHED AND source.Price > 0 THEN
    INSERT (ProductId, ProductName, Price)
    VALUES (source.ProductId, source.ProductName, source.Price);

Error Handling:

BEGIN TRY
    BEGIN TRANSACTION;
    
    MERGE INTO Products AS target
    USING #NewProducts AS source
    ON target.ProductId = source.ProductId
    WHEN MATCHED THEN UPDATE SET Price = source.Price
    WHEN NOT MATCHED THEN INSERT VALUES (source.ProductId, source.ProductName, source.Price);
    
    COMMIT TRANSACTION;
    PRINT 'Merge successful';
    
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    PRINT 'Merge failed: ' + @ErrorMessage;
    
END CATCH;

Alternative for MySQL – INSERT ON DUPLICATE KEY:

-- MySQL doesn't have MERGE, use this instead:
INSERT INTO Products (ProductId, ProductName, Price)
VALUES (1, 'Laptop', 1200.00)
ON DUPLICATE KEY UPDATE
    ProductName = VALUES(ProductName),
    Price = VALUES(Price);

-- Requires UNIQUE or PRIMARY KEY on ProductId

PostgreSQL – INSERT ON CONFLICT:

-- PostgreSQL equivalent:
INSERT INTO Products (ProductId, ProductName, Price)
VALUES (1, 'Laptop', 1200.00)
ON CONFLICT (ProductId)
DO UPDATE SET
    ProductName = EXCLUDED.ProductName,
    Price = EXCLUDED.Price;

-- EXCLUDED refers to the row that would have been inserted

Performance Comparison:

-- Test: Upsert 100,000 rows

-- Method 1: IF EXISTS + UPDATE/INSERT
-- Time: 45 seconds
-- Locks: 200,000 (check + action per row)

-- Method 2: MERGE
-- Time: 8 seconds (5.6x faster!)
-- Locks: 100,000 (one per row)

-- Why faster:
-- - Single statement = less overhead
-- - Atomic operation = better locking
-- - Optimized execution plan