MERGE and Upsert Patterns in SQL Server

UPSERT = UPDATE if the row exists, otherwise INSERT.

MERGE does INSERT / UPDATE / DELETE in ONE statement by comparing a
SOURCE (new data) with a TARGET (existing table):

MERGE Target AS t
USING Source AS s ON t.Key = s.Key
WHEN MATCHED [AND condition]       THEN UPDATE ...
WHEN NOT MATCHED BY TARGET         THEN INSERT ...
WHEN NOT MATCHED BY SOURCE         THEN DELETE ...   (optional)
OUTPUT $action, inserted.*, deleted.*;               (optional)
;   <-- MERGE MUST end with a semicolon

Typical data engineering use : loading a daily file (staging) into a dimension / master table (incremental load, SCD Type 1 & 2).

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic tables

DROP TABLE IF EXISTS dbo.MergeLog;
DROP TABLE IF EXISTS dbo.ProductMaster;
DROP TABLE IF EXISTS dbo.ProductStaging;
DROP TABLE IF EXISTS dbo.DimCustomer;
DROP TABLE IF EXISTS dbo.CustomerStaging;

-- TARGET : current product master
CREATE TABLE dbo.ProductMaster
(
    ProductCode  VARCHAR(10)   PRIMARY KEY,
    ProductName  VARCHAR(100)  NOT NULL,
    Category     VARCHAR(50)   NOT NULL,
    Price        DECIMAL(10,2) NOT NULL,
    IsActive     BIT           NOT NULL DEFAULT 1,
    LastUpdated  DATETIME2(0)  NOT NULL DEFAULT SYSDATETIME()
);

INSERT INTO dbo.ProductMaster (ProductCode, ProductName, Category, Price, LastUpdated) VALUES
('P001', 'Dell Inspiron 15',   'Laptops',     65000, '2025-01-01'),
('P002', 'HP Pavilion 14',     'Laptops',     85000, '2025-01-01'),
('P003', 'Wireless Mouse',     'Accessories',  1200, '2025-01-01'),
('P004', 'USB-C Hub',          'Accessories',  3500, '2025-01-01'),
('P005', 'Basic Office Chair', 'Furniture',    8500, '2025-01-01'),
('P006', 'Old Model Tablet',   'Mobiles',     18000, '2025-01-01');

-- SOURCE : today's file from the supplier
CREATE TABLE dbo.ProductStaging
(
    ProductCode VARCHAR(10)   NOT NULL,
    ProductName VARCHAR(100)  NOT NULL,
    Category    VARCHAR(50)   NOT NULL,
    Price       DECIMAL(10,2) NOT NULL
);

INSERT INTO dbo.ProductStaging (ProductCode, ProductName, Category, Price) VALUES
('P001', 'Dell Inspiron 15',      'Laptops',     63000),   -- price changed   -> UPDATE
('P002', 'HP Pavilion 14',        'Laptops',     85000),   -- no change       -> skip
('P003', 'Wireless Mouse Pro',    'Accessories',  1500),   -- name+price chg  -> UPDATE
('P004', 'USB-C Hub',             'Accessories',  3500),   -- no change       -> skip
('P005', 'Basic Office Chair',    'Furniture',    8200),   -- price changed   -> UPDATE
('P007', 'Standing Desk',         'Furniture',   22000),   -- new             -> INSERT
('P008', 'Mechanical Keyboard',   'Accessories',  2500);   -- new             -> INSERT
-- P006 is NOT in the file -> deactivate (soft delete)

CREATE TABLE dbo.MergeLog
(
    LogID       INT IDENTITY(1,1) PRIMARY KEY,
    ActionTaken NVARCHAR(10),
    ProductCode VARCHAR(10),
    OldPrice    DECIMAL(10,2),
    NewPrice    DECIMAL(10,2),
    LoggedAt    DATETIME2(0) DEFAULT SYSDATETIME()
);
GO

(6 rows affected)
(7 rows affected)

Part A: FULL MERGE (insert + update + soft delete) with OUTPUT

SELECT 'BEFORE' AS Stage, * FROM dbo.ProductMaster;

MERGE dbo.ProductMaster AS t
USING dbo.ProductStaging AS s
      ON t.ProductCode = s.ProductCode
WHEN MATCHED AND (t.ProductName <> s.ProductName OR t.Price <> s.Price OR t.Category <> s.Category OR t.IsActive = 0)
    THEN UPDATE SET t.ProductName = s.ProductName,
                    t.Category    = s.Category,
                    t.Price       = s.Price,
                    t.IsActive    = 1,
                    t.LastUpdated = SYSDATETIME()
WHEN NOT MATCHED BY TARGET
    THEN INSERT (ProductCode, ProductName, Category, Price)
         VALUES (s.ProductCode, s.ProductName, s.Category, s.Price)
WHEN NOT MATCHED BY SOURCE AND t.IsActive = 1
    THEN UPDATE SET t.IsActive = 0, t.LastUpdated = SYSDATETIME()     -- soft delete
OUTPUT $action,
       COALESCE(inserted.ProductCode, deleted.ProductCode),
       deleted.Price,
       inserted.Price
INTO dbo.MergeLog (ActionTaken, ProductCode, OldPrice, NewPrice);

SELECT 'AFTER' AS Stage, * FROM dbo.ProductMaster;
SELECT * FROM dbo.MergeLog;                 -- what MERGE did, row by row
SELECT ActionTaken, COUNT(*) AS Cnt FROM dbo.MergeLog GROUP BY ActionTaken;
GO

-- Run the same MERGE again : nothing changes (idempotent load = good ETL design)

(6 rows affected)

Part B: Upsert a Single Row (e.g. from an application)

MERGE version (HOLDLOCK prevents race conditions between sessions)

DECLARE @Code VARCHAR(10) = 'P009', @Name VARCHAR(100) = 'Ergonomic Chair', @Cat VARCHAR(50) = 'Furniture', @Price DECIMAL(10,2) = 12000;

MERGE dbo.ProductMaster WITH (HOLDLOCK) AS t
USING (SELECT @Code AS ProductCode) AS s
      ON t.ProductCode = s.ProductCode
WHEN MATCHED     THEN UPDATE SET ProductName = @Name, Category = @Cat, Price = @Price, LastUpdated = SYSDATETIME()
WHEN NOT MATCHED THEN INSERT (ProductCode, ProductName, Category, Price) VALUES (@Code, @Name, @Cat, @Price);

SELECT * FROM dbo.ProductMaster WHERE ProductCode = @Code;
GO

(1 row affected)

UPDATE-then-INSERT pattern (no MERGE). Popular and easy to read.

DECLARE @Code VARCHAR(10) = 'P009', @Price DECIMAL(10,2) = 11500;

BEGIN TRAN;
    UPDATE dbo.ProductMaster WITH (UPDLOCK, SERIALIZABLE)
    SET Price = @Price, LastUpdated = SYSDATETIME()
    WHERE ProductCode = @Code;

    IF @@ROWCOUNT = 0
        INSERT INTO dbo.ProductMaster (ProductCode, ProductName, Category, Price)
        VALUES (@Code, 'Ergonomic Chair', 'Furniture', @Price);
COMMIT TRAN;

SELECT * FROM dbo.ProductMaster WHERE ProductCode = @Code;
GO

(1 row affected)

Set-based UPDATE + INSERT (alternative to MERGE for bulk loads)

BEGIN TRAN;
    UPDATE t
    SET t.Price = s.Price, t.ProductName = s.ProductName, t.LastUpdated = SYSDATETIME()
    FROM dbo.ProductMaster t
    JOIN dbo.ProductStaging s ON s.ProductCode = t.ProductCode
    WHERE t.Price <> s.Price OR t.ProductName <> s.ProductName;

    INSERT INTO dbo.ProductMaster (ProductCode, ProductName, Category, Price)
    SELECT s.ProductCode, s.ProductName, s.Category, s.Price
    FROM dbo.ProductStaging s
    WHERE NOT EXISTS (SELECT 1 FROM dbo.ProductMaster t WHERE t.ProductCode = s.ProductCode);
COMMIT TRAN;
GO

(0 rows affected)
(0 rows affected)

Part C: Slowly Changing Dimension Type 2 (keep history)

When a customer's City changes, close the old row and insert a new row.

CREATE TABLE dbo.DimCustomer
(
    CustomerSK   INT IDENTITY(1,1) PRIMARY KEY,     -- surrogate key
    CustomerID   INT          NOT NULL,             -- business key
    CustomerName VARCHAR(100) NOT NULL,
    City         VARCHAR(50)  NOT NULL,
    Segment      VARCHAR(20)  NOT NULL,
    ValidFrom    DATE         NOT NULL,
    ValidTo      DATE         NULL,                 -- NULL = current
    IsCurrent    BIT          NOT NULL DEFAULT 1
);

INSERT INTO dbo.DimCustomer (CustomerID, CustomerName, City, Segment, ValidFrom)
SELECT CustomerID, CustomerName, City, Segment, '2024-01-01'
FROM dbo.Customers WHERE CustomerID <= 5;

CREATE TABLE dbo.CustomerStaging
(
    CustomerID INT, CustomerName VARCHAR(100), City VARCHAR(50), Segment VARCHAR(20)
);

INSERT INTO dbo.CustomerStaging VALUES
(1, 'Ravi Traders',       'Secunderabad', 'SMB'),        -- city changed     -> new version
(2, 'Infosoft Solutions', 'Bengaluru',    'Corporate'),  -- same             -> nothing
(3, 'Lakshmi Stores',     'Chennai',      'SMB'),        -- segment changed  -> new version
(6, 'Capital Electronics','Delhi',        'SMB');        -- brand new        -> insert
GO

DECLARE @LoadDate DATE = '2025-07-01';

-- Step 1 : MERGE closes changed rows and inserts brand new customers.
--          OUTPUT returns the closed rows so we can insert their new version.
INSERT INTO dbo.DimCustomer (CustomerID, CustomerName, City, Segment, ValidFrom)
SELECT CustomerID, CustomerName, City, Segment, @LoadDate
FROM (
        MERGE dbo.DimCustomer AS t
        USING dbo.CustomerStaging AS s
              ON t.CustomerID = s.CustomerID AND t.IsCurrent = 1
        WHEN MATCHED AND (t.City <> s.City OR t.Segment <> s.Segment)
            THEN UPDATE SET t.IsCurrent = 0, t.ValidTo = DATEADD(DAY, -1, @LoadDate)
        WHEN NOT MATCHED BY TARGET
            THEN INSERT (CustomerID, CustomerName, City, Segment, ValidFrom)
                 VALUES (s.CustomerID, s.CustomerName, s.City, s.Segment, @LoadDate)
        OUTPUT $action AS MergeAction, s.CustomerID, s.CustomerName, s.City, s.Segment
     ) AS changes
WHERE MergeAction = 'UPDATE';

SELECT * FROM dbo.DimCustomer ORDER BY CustomerID, ValidFrom;

-- Point-in-time query : which city was customer 1 in on 2025-03-15?
SELECT CustomerID, City
FROM dbo.DimCustomer
WHERE CustomerID = 1
  AND '2025-03-15' >= ValidFrom AND ('2025-03-15' <= ValidTo OR ValidTo IS NULL);
GO

(5 rows affected)
(4 rows affected)
(2 rows affected)

MERGE Tips

• Source must have UNIQUE keys, otherwise error: "attempted to UPDATE or DELETE the same row more than once".
• Always add "AND (changed columns)" to WHEN MATCHED to avoid useless updates.
• Use WITH (HOLDLOCK) for concurrent single-row upserts.
• Careful with NULLs when comparing columns: use ISNULL or "EXISTS (SELECT s.cols EXCEPT SELECT t.cols)".

PRACTICE QUESTIONS 1. Add a duplicate row to ProductStaging and run the MERGE - read the error, then fix it with ROW_NUMBER. 2. Change the MERGE to HARD delete products missing from the file. 3. Build a daily sales summary table and MERGE dbo.Sales into it (upsert by SaleDate + Region). 4. Rewrite the change detection using "EXISTS (SELECT s.* EXCEPT SELECT t.*)" to handle NULLs.