Narcis Miclaus

8 min read

SQL Server, from the ground up Lesson 17 / 40

INSERT, UPDATE, DELETE, and why MERGE is dangerous

The four DML verbs, the UPSERT patterns that actually work, the OUTPUT clause, and the long list of reasons to avoid MERGE.

DML — Data Manipulation Language — is the other half of SQL. Up to now we’ve been reading. Now we’re writing. INSERT, UPDATE, DELETE, and the controversial MERGE. Plus the OUTPUT clause, which turns any of them into a “tell me what you changed” operation.

Runehold writes data every second of every day. New customer signups, new orders, inventory adjustments, shipment status updates, returns, refunds. How this is done — atomically, safely, and with the right audit trail — is today’s topic.

INSERT

Single row

INSERT INTO Sales.Customer (Name, Email, Country)
VALUES (N'Maria Rossi', N'maria@example.it', 'IT');

Column list explicit, VALUES clause explicit. You can omit the column list to insert by position (matches the table definition), but that’s fragile — adding a column later silently breaks every query. Always list columns.

Multiple rows

INSERT INTO Sales.Customer (Name, Email, Country)
VALUES
    (N'Maria Rossi',   N'maria@example.it',  'IT'),
    (N'Hans Schmidt',  N'hans@example.de',   'DE'),
    (N'Liesbeth Jong', N'liesbeth@nl.com',   'NL');

One statement, three rows. Faster than three separate INSERTs because SQL Server logs it as a single operation.

Insert from a SELECT

-- Copy customers into a backup table
INSERT INTO Sales.Customer_Backup (CustomerId, Name, Email, Country, BackedUpAt)
SELECT CustomerId, Name, Email, Country, SYSUTCDATETIME()
FROM Sales.Customer;

-- Migrate staging data into production
INSERT INTO Sales.Customer (Name, Email, Country)
SELECT Name, Email, Country
FROM Staging.CustomerImport
WHERE IsValid = 1;

The most common bulk-loading pattern. Be explicit about columns on both sides.

Get the identity of the inserted row

-- Old way: SCOPE_IDENTITY()
INSERT INTO Sales.Orders (CustomerId, Total, CountryCode, VatRate)
VALUES (@cust, @total, @country, @vat);
DECLARE @newId BIGINT = SCOPE_IDENTITY();

-- Modern way: OUTPUT
INSERT INTO Sales.Orders (CustomerId, Total, CountryCode, VatRate)
OUTPUT INSERTED.OrderId, INSERTED.OrderDate
VALUES (@cust, @total, @country, @vat);

OUTPUT INSERTED.* returns the newly-inserted rows. Works for bulk inserts too — you get one output row per inserted row. Useful for returning new IDs to the application, or for writing to an audit table in the same statement.

UPDATE

Simple update

UPDATE Sales.Customer
SET    Email = N'maria.new@example.it',
       UpdatedAt = SYSUTCDATETIME(),
       UpdatedBy = SUSER_SNAME()
WHERE  CustomerId = 12345;

Always have a WHERE clause. Forgetting it updates every row in the table. This is the single most common disastrous mistake in SQL. Before running any UPDATE or DELETE, I do two things:

  1. Copy the statement, replace UPDATE ... SET ... with SELECT *, and verify the WHERE returns the rows I expect.
  2. Wrap the real statement in BEGIN TRAN; ...; ROLLBACK;, inspect row counts, and only commit when I’m sure.

Habits of the paranoid save careers.

Update with a JOIN

-- Update the country code on orders based on the customer's country
UPDATE o
SET o.CountryCode = c.Country
FROM Sales.Orders AS o
JOIN Sales.Customer AS c ON c.CustomerId = o.CustomerId
WHERE o.CountryCode IS NULL;

Weird syntax, SQL-Server-specific: UPDATE <alias> followed by SET and then FROM. It’s the de facto standard for T-SQL and nobody questions it in code review.

OUTPUT on UPDATE

Same pattern as INSERT:

UPDATE Sales.Orders
SET Status = 2
OUTPUT INSERTED.OrderId, INSERTED.Status AS new_status, DELETED.Status AS old_status
WHERE Status = 1 AND OrderDate < DATEADD(DAY, -7, GETDATE());

INSERTED contains the row after the update, DELETED contains it before. Perfect for audit logs: “who changed what from what to what.”

DELETE

DELETE FROM Sales.Orders
WHERE Status = 9 AND OrderDate < DATEADD(YEAR, -7, GETDATE());

Same warning applies: WHERE clause or you delete everything.

TRUNCATE TABLE is faster than DELETE without a WHERE, but drops foreign-key relationships temporarily (we covered this in lesson 5). TRUNCATE if you want all rows gone and the table empty; DELETE ... WHERE for everything else.

DELETE with a JOIN

-- Delete orders whose customer no longer exists (if FK allows)
DELETE o
FROM Sales.Orders AS o
LEFT JOIN Sales.Customer AS c ON c.CustomerId = o.CustomerId
WHERE c.CustomerId IS NULL;

Same two-part syntax as UPDATE. Works cleanly once you’re used to it.

OUTPUT on DELETE

-- Archive orders before deleting
DELETE FROM Sales.Orders
OUTPUT DELETED.*
INTO Sales.Orders_Archive
WHERE OrderDate < DATEADD(YEAR, -7, GETDATE());

OUTPUT ... INTO <table> redirects the output directly into another table. Atomic delete + archive in one statement. For data-retention workflows, this pattern is gold.

The UPSERT problem

“Insert if not exists, otherwise update.” Every application needs this. The simple solution looks wrong but is actually right:

-- The "IF EXISTS" pattern, safe and readable
IF EXISTS (SELECT 1 FROM Sales.Customer WHERE Email = @email)
BEGIN
    UPDATE Sales.Customer
    SET    Name = @name, UpdatedAt = SYSUTCDATETIME()
    WHERE  Email = @email;
END
ELSE
BEGIN
    INSERT INTO Sales.Customer (Name, Email, Country)
    VALUES (@name, @email, @country);
END;

Simple, explicit, safe. Wrap it in a transaction and you’re done.

Issue: under concurrent inserts, two sessions could both pass the IF EXISTS check and both try to INSERT, one gets a unique-constraint violation. For low-traffic tables, fine. For hot tables, add HOLDLOCK:

BEGIN TRAN;

    IF EXISTS (SELECT 1 FROM Sales.Customer WITH (UPDLOCK, HOLDLOCK) WHERE Email = @email)
        UPDATE Sales.Customer SET ... WHERE Email = @email;
    ELSE
        INSERT INTO Sales.Customer (Name, Email, Country) VALUES (@name, @email, @country);

COMMIT;

UPDLOCK, HOLDLOCK ensures no one else can sneak in between the EXISTS check and the insert. We’ll cover locking properly in lesson 20.

Alternative: let the unique constraint do the work and catch the error:

BEGIN TRY
    INSERT INTO Sales.Customer (Name, Email, Country) VALUES (@name, @email, @country);
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 2627   -- unique constraint violation
        UPDATE Sales.Customer SET Name = @name WHERE Email = @email;
    ELSE
        THROW;
END CATCH;

Optimistic: try insert first, fall back to update if it collides. Fine for tables where collisions are rare.

MERGE: why it’s dangerous

MERGE tries to do UPSERT in one statement:

MERGE INTO Sales.Customer AS target
USING (SELECT @email AS Email, @name AS Name, @country AS Country) AS src
ON target.Email = src.Email
WHEN MATCHED THEN
    UPDATE SET Name = src.Name, UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED THEN
    INSERT (Name, Email, Country) VALUES (src.Name, src.Email, src.Country);

Elegant, concise, ill-advised.

The list of reasons to avoid MERGE in SQL Server:

  • Multiple documented bugs where MERGE returns wrong results or silently does nothing. Aaron Bertrand has kept a running list for 15 years. Some are fixed; others persist.
  • Concurrency issues that are worse than in IF EXISTS + INSERT/UPDATE.
  • Interacts badly with filtered indexes, indexed views, and INSTEAD OF triggers.
  • The WHEN NOT MATCHED BY SOURCE clause can silently delete more than you expected.
  • Harder to read and debug than the explicit IF/ELSE pattern.

Even Microsoft’s own guidance has quietly de-emphasized MERGE. If you see it in existing code, understand what it does but don’t reach for it in new code. The safe patterns are IF EXISTS + INSERT/UPDATE or INSERT + try/catch.

If you absolutely must use MERGE (maybe for a one-time ETL script), read the existing bug list first. I’m not kidding.

Batched updates for big tables

Running UPDATE Orders SET Status = 9 WHERE OrderDate < ... on a table with 200 million matching rows will lock the table, fill the log, and make every user wait.

Batch it:

WHILE 1 = 1
BEGIN
    UPDATE TOP (10000) Sales.Orders
    SET Status = 9
    WHERE Status = 1 AND OrderDate < '2020-01-01';

    IF @@ROWCOUNT = 0 BREAK;

    -- Let other queries breathe
    WAITFOR DELAY '00:00:00.100';
END;

10,000 rows per batch, 100ms pause. Small transactions, small log spikes, no table-level lock escalation. Same pattern for big deletes.

For deletes-of-most-rows, another approach: create a new table with only the rows you want, drop the old, rename the new. Often faster than deleting row-by-row.

The OUTPUT clause in full

Summary of OUTPUT uses:

-- Return changes to the client
INSERT INTO ... OUTPUT INSERTED.Id ... VALUES ...;
UPDATE ...    OUTPUT DELETED.OldVal, INSERTED.NewVal WHERE ...;
DELETE FROM ... OUTPUT DELETED.* WHERE ...;

-- Redirect changes into a table (for audit, archive, etc.)
UPDATE ...
OUTPUT DELETED.*, INSERTED.*, SUSER_SNAME() AS changed_by, SYSUTCDATETIME() AS changed_at
INTO Audit.CustomerChanges
WHERE ...;

Anything you can see in DELETED. and INSERTED. is fair game. A common use case: an audit table that captures before/after of every change, written by the same statement that made the change.

Run this on your own machine

USE Runehold;
GO

-- 1. Basic INSERT with multiple rows
INSERT INTO Sales.Customer (Name, Country)
VALUES
    (N'Elena García', 'ES'),
    (N'Pierre Dupont', 'FR'),
    (N'Lars Hansen',  'SE');

-- 2. UPDATE with JOIN + OUTPUT
UPDATE o
SET o.Notes = CONCAT(o.Notes, N'[' + c.Country + N']')
OUTPUT INSERTED.OrderId, DELETED.Notes AS old_notes, INSERTED.Notes AS new_notes
FROM Sales.Orders AS o
JOIN Sales.Customer AS c ON c.CustomerId = o.CustomerId
WHERE o.Notes IS NULL OR o.Notes NOT LIKE N'%[%]%';

-- 3. DELETE with OUTPUT to an archive table
IF OBJECT_ID('Sales.Orders_Archive', 'U') IS NULL
BEGIN
    SELECT TOP 0 * INTO Sales.Orders_Archive FROM Sales.Orders;
END;

DELETE FROM Sales.Orders
OUTPUT DELETED.*
INTO Sales.Orders_Archive
WHERE OrderDate < '2026-01-01';

SELECT COUNT(*) FROM Sales.Orders_Archive;

-- 4. UPSERT the safe way, wrapped in a transaction
DECLARE @email NVARCHAR(200) = N'newcomer@example.com';
DECLARE @name  NVARCHAR(100) = N'Newcomer One';
DECLARE @country CHAR(2)     = 'PT';

BEGIN TRAN;
    IF EXISTS (
        SELECT 1 FROM Sales.Customer WITH (UPDLOCK, HOLDLOCK) WHERE Name = @name
    )
        UPDATE Sales.Customer
        SET Country = @country
        WHERE Name = @name;
    ELSE
        INSERT INTO Sales.Customer (Name, Country) VALUES (@name, @country);
COMMIT;

-- 5. Batched update pattern (runs once since data is small)
WHILE 1 = 1
BEGIN
    UPDATE TOP (100) Sales.Orders
    SET Notes = COALESCE(Notes, N'') + N' '
    WHERE Notes IS NULL OR Notes NOT LIKE '% ';

    IF @@ROWCOUNT = 0 BREAK;
END;

Every DML statement should be something you’d run in front of your team. “I’ll update everyone’s record” is never the goal; “I’ll update the 47 rows matching this filter” is. Always know the row count before you commit.

Next lesson: transactions and ACID. What “atomic” actually means when the server crashes mid-commit, and why your BEGIN TRAN + COMMIT habit is the DBA’s best friend.

Search