Day 15: Advanced INSERT, UPDATE, DELETE

A. Lesson overview

Activity breakdown

Business scenario. It is load day. The cleaned customer import (Day 13) must be applied to the customer table: new customers inserted, changed contact details updated, duplicates ignored. Merchandising’s validated price changes (Day 9) must be applied and audited. A stock summary table needs a nightly refresh. Every change must be previewed, reversible until verified, and leave an audit trail of exactly what changed.

Working copies. All of today’s changes are made to copies in the Lab schema. The core tables are never modified, and the setup in section C recreates the copies at any time.

B. Theory explained clearly

Part 1 — Primer: modifying data safely

A professional routine for any change to real data:

-- 1. Preview: the same FROM/WHERE as the change, as a SELECT
SELECT … FROM Lab.Product WHERE …;               -- expect N rows

-- 2. Change inside a transaction
BEGIN TRANSACTION;

UPDATE Lab.Product SET … WHERE …;
SELECT @@ROWCOUNT AS RowsAffected;               -- 3. check: N?

SELECT … ;                                       -- 4. verify the new state

-- 5. Decide
ROLLBACK TRANSACTION;   -- while practising, or if anything looks wrong
-- COMMIT TRANSACTION;  -- when you are sure

Never leave a transaction open. If you run BEGIN TRANSACTION and forget to commit or roll back, your session keeps locks and other users wait. In SSMS, closing the query window asks whether to commit. SELECT @@TRANCOUNT; shows how many transactions your session has open (0 = none).

Two more safety habits:

• Write the WHERE first. An UPDATE or DELETE without WHERE affects every row.
• Prefer set-based statements. One UPDATE … FROM … JOIN changes thousands of rows consistently; row-by-row loops are slower and easier to get wrong.

Part 2 — INSERT forms and identity values

Always list the target columns. Without the list, the insert depends on the table’s column order and breaks when a column is added.

Identity columns generate numbers automatically (IDENTITY(1,1)). After an insert:

The core RetailLab tables use explicit keys (so data is identical everywhere); today’s Lab.ImportBatch table shows identity in action.

Part 3 — UPDATE and DELETE with joins and CTEs

T-SQL lets UPDATE and DELETE use a FROM clause with joins:

UPDATE p
SET    p.ListPrice = s.NewListPrice
FROM   Lab.Product AS p
JOIN   ProposedPrices AS s ON s.ProductCode = p.ProductCode
WHERE  p.ListPrice <> s.NewListPrice;

• Name the alias after UPDATE/DELETE (UPDATE p, DELETE c) so it is clear which table changes.
• If a target row joins to several source rows, UPDATE … FROM silently uses one of them (which one is not defined). De-duplicate the source first. (MERGE raises an error instead — see Part 5.)
• You can update or delete through a CTE that reads from a single table — the classic de-duplication:

WITH Numbered AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY TRIM(Email) ORDER BY ImportRowID) AS rn
    FROM   Lab.CustomerImportCopy
)
DELETE FROM Numbered WHERE rn > 1;          -- deletes from the underlying table

TOP (n) limits how many rows an UPDATE/DELETE affects — useful for deleting in batches (stretch challenge). Because TOP in a modification cannot have its own ORDER BY, which rows are chosen is arbitrary; that is fine when every qualifying row will eventually be processed.

Part 4 — The OUTPUT clause

OUTPUT returns rows as they are modified, using two virtual tables:

UPDATE Lab.Product
SET    ListPrice = ListPrice * 1.05
OUTPUT deleted.ProductCode, deleted.ListPrice AS OldPrice, inserted.ListPrice AS NewPrice
WHERE  CategoryID = 2;

OUTPUT … INTO @table_variable or INTO #temp_table or INTO Lab.SomeTable stores the rows instead of returning them — an audit trail, or the input for the next step. Restrictions on an OUTPUT INTO target: it cannot have enabled triggers, take part in a foreign key, or have check constraints or enabled rules. In an INSERT … SELECT, OUTPUT can only refer to inserted columns — not to columns of the source query that were not inserted; MERGE can (useful for mapping old keys to new ones).

Part 5 — MERGE

Syntax

MERGE Lab.StockSummary AS t                         -- target
USING (SELECT ProductID, SUM(QuantityOnHand) AS TotalOnHand
       FROM   Inventory.Stock GROUP BY ProductID) AS s  -- source
   ON t.ProductID = s.ProductID                     -- match condition: keys only
WHEN MATCHED AND t.TotalOnHand <> s.TotalOnHand THEN
     UPDATE SET t.TotalOnHand = s.TotalOnHand
WHEN NOT MATCHED BY TARGET THEN                      -- in source, not in target
     INSERT (ProductID, TotalOnHand) VALUES (s.ProductID, s.TotalOnHand)
WHEN NOT MATCHED BY SOURCE THEN                      -- in target, not in source
     DELETE
OUTPUT $action, inserted.ProductID, deleted.ProductID;   -- $action = INSERT / UPDATE / DELETE

• MERGE must end with a semicolon (error 10713 otherwise).
• At most one WHEN MATCHED … UPDATE and one … DELETE (each may have an extra AND condition), one NOT MATCHED BY TARGET, and up to two NOT MATCHED BY SOURCE clauses.
• $action in OUTPUT tells you which branch touched each row — the recommended way to count what a MERGE did.

Documented cautions

From Microsoft’s MERGE documentation:

1. Put only matching columns in ON. Filtering the target in the ON clause (“AND t.Active = 1”) “can return unexpected and incorrect results”. Put filters in a CTE that defines the target, or in the WHEN … AND … conditions.
2. One source row per target row. If several source rows match one target row, MERGE fails (error 8672: The MERGE statement attempted to UPDATE or DELETE the same row more than once). De-duplicate the source.
3. Concurrency. Two sessions running the same “update if exists, else insert” MERGE at the same moment can both decide “not exists” and both insert — causing duplicate-key errors. Microsoft notes that WITH (HOLDLOCK) on the target (equivalent to SERIALIZABLE) prevents this in such upsert scenarios, and advises thorough testing because MERGE at scale can introduce complicated concurrency issues.
4. Simple jobs don’t need it. “When simply updating one table based on the rows of another table, improve the performance and scalability with INSERT, UPDATE, and DELETE statements.”
5. Triggers: AFTER triggers for each action fire, in no guaranteed order; @@ROWCOUNT inside them reflects the whole MERGE.

The alternative: separate statements in one transaction

SET XACT_ABORT ON;            -- any run-time error rolls back the whole transaction (Day 20)
BEGIN TRANSACTION;

UPDATE t
SET    t.TotalOnHand = s.TotalOnHand
FROM   Lab.StockSummary AS t WITH (UPDLOCK, HOLDLOCK)
JOIN   Source AS s ON s.ProductID = t.ProductID
WHERE  t.TotalOnHand <> s.TotalOnHand;

INSERT INTO Lab.StockSummary (ProductID, TotalOnHand)
SELECT s.ProductID, s.TotalOnHand
FROM   Source AS s
WHERE  NOT EXISTS (SELECT 1 FROM Lab.StockSummary AS t WITH (UPDLOCK, HOLDLOCK) WHERE t.ProductID = s.ProductID);

DELETE t
FROM   Lab.StockSummary AS t
WHERE  NOT EXISTS (SELECT 1 FROM Source AS s WHERE s.ProductID = t.ProductID);

COMMIT TRANSACTION;

Each statement is simple and separately testable. UPDLOCK, HOLDLOCK keeps the rows (and key ranges) you checked locked until the transaction ends, so another session cannot insert the same key between your check and your insert. Day 16 explains these locks.

Part 6 — Temporary tables and table variables

Use table variables for small sets and for capturing results that must survive a rollback (error logging, Day 20). Use temporary tables for larger intermediate results that later queries join to or filter heavily.

C. Dataset and starting state

Setup — run once now, and again whenever you want a fresh start. It drops and recreates only the Lab copies (destructive for those tables only).

USE RetailLab;
GO

DROP TABLE IF EXISTS Lab.Product, Lab.Customer, Lab.StockSummary, Lab.ProductArchive,
                     Lab.CustomerImportCopy, Lab.ImportBatch;
GO

-- Copies of core tables (SELECT INTO copies columns and data, not constraints)
SELECT * INTO Lab.Product            FROM Inventory.Product;
SELECT * INTO Lab.Customer           FROM Sales.Customer;
SELECT * INTO Lab.CustomerImportCopy FROM Staging.CustomerImport;

ALTER TABLE Lab.Product            ADD CONSTRAINT PK_LabProduct  PRIMARY KEY (ProductID);
ALTER TABLE Lab.Customer           ADD CONSTRAINT PK_LabCustomer PRIMARY KEY (CustomerID);
ALTER TABLE Lab.CustomerImportCopy ADD CONSTRAINT PK_LabCustomerImportCopy PRIMARY KEY (ImportRowID);

-- Archive table with the same columns as Lab.Product, plus when it was archived
SELECT TOP (0) * INTO Lab.ProductArchive FROM Inventory.Product;
ALTER TABLE Lab.ProductArchive ADD ArchivedAt datetime2(0) NULL;

-- A summary table for the MERGE examples, deliberately made stale:
SELECT ProductID, SUM(QuantityOnHand) AS TotalOnHand
INTO   Lab.StockSummary
FROM   Inventory.Stock
GROUP BY ProductID;
ALTER TABLE Lab.StockSummary ADD CONSTRAINT PK_LabStockSummary PRIMARY KEY (ProductID);
DELETE FROM Lab.StockSummary WHERE ProductID = 4;                    -- missing row
UPDATE Lab.StockSummary SET TotalOnHand = 999 WHERE ProductID IN (1, 2);  -- wrong values
INSERT INTO Lab.StockSummary (ProductID, TotalOnHand) VALUES (99, 5);     -- row with no source

-- A table with an IDENTITY column
CREATE TABLE Lab.ImportBatch
(
    BatchID     int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ImportBatch PRIMARY KEY,
    SourceTable sysname           NOT NULL,
    RowsLoaded  int               NOT NULL,
    LoadedAt    datetime2(0)      NOT NULL CONSTRAINT DF_ImportBatch_LoadedAt DEFAULT (SYSUTCDATETIME())
);
GO

Validation. Expected: 30, 40, 29, 15, 0, 0.

SELECT  (SELECT COUNT(*) FROM Lab.Product)            AS LabProducts,
        (SELECT COUNT(*) FROM Lab.Customer)           AS LabCustomers,
        (SELECT COUNT(*) FROM Lab.StockSummary)       AS StockSummaryRows,
        (SELECT COUNT(*) FROM Lab.CustomerImportCopy) AS ImportCopyRows,
        (SELECT COUNT(*) FROM Lab.ProductArchive)     AS ArchivedProducts,
        @@TRANCOUNT                                   AS OpenTransactions;

Lab.StockSummary has 29 rows: 29 stocked products, minus product 4, plus the bogus product 99.

D. Worked examples

Worked example 1 — INSERT with OUTPUT and an identity value

INSERT INTO Lab.ImportBatch (SourceTable, RowsLoaded)
OUTPUT inserted.BatchID, inserted.SourceTable, inserted.RowsLoaded
VALUES (N'Staging.CustomerImport', 15),
       (N'Staging.ProductPriceUpdate', 10);

SELECT SCOPE_IDENTITY() AS LastIdentityInScope;

Expected result: the OUTPUT returns two rows with BatchID 1 and 2; SCOPE_IDENTITY() returns 2 — only the last value, which is why OUTPUT is the reliable choice for multi-row inserts. (If you run the example twice, the IDs continue: 3 and 4. Identity values are never reused automatically, even after a rollback.)

Worked example 2 — UPDATE from a join, with an audit of old and new values

-- Preview: the valid, latest price per known product code (Day 9's stretch)
WITH Latest AS
(
    SELECT  UPPER(TRIM(ProductCode))                    AS ProductCode,
            TRY_CAST(NewListPriceText AS decimal(10,2)) AS NewListPrice,
            ROW_NUMBER() OVER (PARTITION BY UPPER(TRIM(ProductCode)) ORDER BY RowID DESC) AS rn
    FROM    Staging.ProductPriceUpdate
    WHERE   ProductCode IS NOT NULL
)
SELECT p.ProductCode, p.ListPrice AS CurrentPrice, l.NewListPrice
FROM   Lab.Product AS p
JOIN   Latest      AS l ON l.ProductCode = p.ProductCode
WHERE  l.rn = 1 AND l.NewListPrice > 0 AND p.ListPrice <> l.NewListPrice
ORDER BY p.ProductCode;                       -- expect 5 rows

-- Change, in a transaction, capturing the audit trail
DECLARE @PriceAudit TABLE (ProductCode varchar(20), OldPrice decimal(10,2), NewPrice decimal(10,2));

BEGIN TRANSACTION;

WITH Latest AS
(
    SELECT  UPPER(TRIM(ProductCode))                    AS ProductCode,
            TRY_CAST(NewListPriceText AS decimal(10,2)) AS NewListPrice,
            ROW_NUMBER() OVER (PARTITION BY UPPER(TRIM(ProductCode)) ORDER BY RowID DESC) AS rn
    FROM    Staging.ProductPriceUpdate
    WHERE   ProductCode IS NOT NULL
)
UPDATE p
SET    p.ListPrice = l.NewListPrice
OUTPUT deleted.ProductCode, deleted.ListPrice, inserted.ListPrice INTO @PriceAudit (ProductCode, OldPrice, NewPrice)
FROM   Lab.Product AS p
JOIN   Latest      AS l ON l.ProductCode = p.ProductCode
WHERE  l.rn = 1 AND l.NewListPrice > 0 AND p.ListPrice <> l.NewListPrice;

SELECT @@ROWCOUNT AS RowsUpdated;             -- 5

SELECT * FROM @PriceAudit ORDER BY ProductCode;

COMMIT TRANSACTION;

Expected result: preview 5 rows; RowsUpdated = 5; the audit lists COK-003 6.50 → 6.75, JKT-005 99.00 → 104.00, LGT-001 39.00 → 42.00, SLP-003 59.00 → 62.50, TNT-001 189.00 → 195.00.

What to notice: the preview and the update share the same FROM/WHERE. @@ROWCOUNT is read immediately after the UPDATE. Because rn = 1 guarantees one source row per product, the update is deterministic.

Worked example 3 — DELETE with OUTPUT INTO an archive

BEGIN TRANSACTION;

DELETE FROM Lab.Product
OUTPUT deleted.ProductID, deleted.ProductCode, deleted.ProductName, deleted.CategoryID,
       deleted.ListPrice, deleted.StandardCost, deleted.Colour, deleted.Size, deleted.WeightKg,
       deleted.LaunchDate, deleted.DiscontinuedDate, deleted.Tags, SYSUTCDATETIME()
INTO   Lab.ProductArchive (ProductID, ProductCode, ProductName, CategoryID, ListPrice, StandardCost,
                           Colour, Size, WeightKg, LaunchDate, DiscontinuedDate, Tags, ArchivedAt)
WHERE  DiscontinuedDate < '2025-01-01';

SELECT @@ROWCOUNT AS RowsArchived;

SELECT ProductID, ProductCode, ArchivedAt FROM Lab.ProductArchive;

COMMIT TRANSACTION;

Expected result: RowsArchived = 1; the archive holds product 30 (TNT-004) with the current UTC time. Lab.Product now has 29 rows. Delete and archive happen in one statement, so they cannot get out of step.

Worked example 4 — De-duplicate through a CTE

-- Preview
WITH Numbered AS
(
    SELECT ImportRowID, Email,
           ROW_NUMBER() OVER (PARTITION BY TRIM(Email) ORDER BY ImportRowID) AS rn
    FROM   Lab.CustomerImportCopy
    WHERE  Email IS NOT NULL
)
SELECT ImportRowID, Email FROM Numbered WHERE rn > 1 ORDER BY ImportRowID;   -- rows 4, 5, 13

-- Delete
WITH Numbered AS
(
    SELECT ImportRowID,
           ROW_NUMBER() OVER (PARTITION BY TRIM(Email) ORDER BY ImportRowID) AS rn
    FROM   Lab.CustomerImportCopy
    WHERE  Email IS NOT NULL
)
DELETE FROM Numbered
OUTPUT deleted.ImportRowID
WHERE  rn > 1;

Expected result: the preview and the OUTPUT both list rows 4, 5 and 13. Lab.CustomerImportCopy now has 12 rows. The WHERE Email IS NOT NULL inside the CTE keeps row 12 (no email) out of the partitioning, so it is never treated as a duplicate.

Worked example 5 — MERGE with $action

SELECT ProductID, TotalOnHand FROM Lab.StockSummary WHERE ProductID IN (1, 2, 4, 99) ORDER BY ProductID;

MERGE Lab.StockSummary AS t
USING (SELECT ProductID, SUM(QuantityOnHand) AS TotalOnHand
       FROM   Inventory.Stock
       GROUP BY ProductID) AS s
   ON t.ProductID = s.ProductID
WHEN MATCHED AND t.TotalOnHand <> s.TotalOnHand THEN
     UPDATE SET t.TotalOnHand = s.TotalOnHand
WHEN NOT MATCHED BY TARGET THEN
     INSERT (ProductID, TotalOnHand) VALUES (s.ProductID, s.TotalOnHand)
WHEN NOT MATCHED BY SOURCE THEN
     DELETE
OUTPUT $action AS MergeAction,
       COALESCE(inserted.ProductID, deleted.ProductID) AS ProductID,
       deleted.TotalOnHand  AS OldTotal,
       inserted.TotalOnHand AS NewTotal;

Expected result: before: products 1 and 2 show 999, product 4 is missing, product 99 shows 5. The MERGE output, in no guaranteed order:

Run the MERGE again: no rows are output — the target already matches. The AND t.TotalOnHand <> s.TotalOnHand condition avoids rewriting unchanged rows.

Worked example 6 — The duplicate-source error and the ON-clause trap

-- Duplicate source rows: LGT-001 appears twice in the staging table
BEGIN TRANSACTION;

MERGE Lab.Product AS t
USING (SELECT UPPER(TRIM(ProductCode)) AS ProductCode,
              TRY_CAST(NewListPriceText AS decimal(10,2)) AS NewListPrice
       FROM   Staging.ProductPriceUpdate
       WHERE  TRY_CAST(NewListPriceText AS decimal(10,2)) > 0) AS s
   ON t.ProductCode = s.ProductCode
WHEN MATCHED THEN UPDATE SET t.ListPrice = s.NewListPrice;

ROLLBACK TRANSACTION;

Expected result: error 8672, The MERGE statement attempted to UPDATE or DELETE the same row more than once… (the two LGT-001 rows both match one product). Run SELECT @@TRANCOUNT; — if it shows 1, run ROLLBACK TRANSACTION; as well: depending on settings, an error may leave the transaction open (Day 20 explains XACT_ABORT).

The ON-clause trap, in words: if you wrote ON t.ProductCode = s.ProductCode AND t.DiscontinuedDate IS NULL together with a WHEN NOT MATCHED BY TARGET THEN INSERT, a source row for a discontinued product would be treated as “not matched” and inserted as a duplicate product. Filters belong in the source/target definition or in the WHEN … AND conditions, never in ON.

Worked example 7 — Table variables ignore ROLLBACK

DECLARE @Log TABLE (Msg nvarchar(100));
CREATE TABLE #Log (Msg nvarchar(100));

BEGIN TRANSACTION;
    INSERT INTO @Log VALUES (N'written inside the transaction');
    INSERT INTO #Log VALUES (N'written inside the transaction');
ROLLBACK TRANSACTION;

SELECT 'table variable' AS Kind, COUNT(*) AS RowsAfterRollback FROM @Log
UNION ALL
SELECT 'temp table', COUNT(*) FROM #Log;

DROP TABLE #Log;

Expected result: table variable 1, temp table 0.

E. Guided practical lab — Load the customer import

Apply the cleaned import to Lab.Customer:

• New customers: valid email not already in Lab.Customer, name present, one row per email (the earliest import row). Assign new IDs after the current maximum. Segment Consumer, joined 2026-01-02 (the load date), active.
• Existing customers: where the import’s phone differs from the stored phone, update it.

Make the setup in section C fresh first if you have already changed Lab.Customer.

Step 1 — Preview the new customers

1. Do: Build the cleaned, de-duplicated, filtered source.
2. Run:

   USE RetailLab;
   GO
   
   WITH Cleaned AS
   (
       SELECT  i.ImportRowID,
               NULLIF(REPLACE(REPLACE(REPLACE(TRIM(i.FullName), N' ', N'<>'), N'><', N''), N'<>', N' '), N'') AS CleanName,
               LOWER(TRIM(i.Email))                                                                         AS CleanEmail,
               i.Phone,
               TRIM(i.City)                                                                                 AS City,
               CASE WHEN UPPER(TRIM(i.Country)) IN (N'UK', N'UNITED KINGDOM') THEN N'United Kingdom'
                    ELSE TRIM(i.Country) END                                                                AS Country
       FROM    Staging.CustomerImport AS i
   ),
   Candidates AS
   (
       SELECT  *, ROW_NUMBER() OVER (PARTITION BY CleanEmail ORDER BY ImportRowID) AS rn
       FROM    Cleaned
       WHERE   CleanName IS NOT NULL
         AND   REGEXP_LIKE(CleanEmail, '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$')
         AND   NOT EXISTS (SELECT 1 FROM Lab.Customer AS c WHERE c.Email = Cleaned.CleanEmail)
   )
   SELECT  ImportRowID, CleanName, CleanEmail, Phone, City, Country
   FROM    Candidates
   WHERE   rn = 1
   ORDER BY ImportRowID;

3. Observe: 4 rows — import rows 3 (Daisy Holt), 6 (Kofi Mensah), 10 (Ines Moreau), 14 (Emma Wilson).
4. Why it matters: Each rule removed something: missing names (rows 8, 9), invalid emails (7, 15), no email (12, fails the regex), existing customers (1, 2, 11), duplicates (4, 5, 13).
5. Confirm: 4 rows.

Step 2 — Insert them, capturing the new IDs

1. Do: Inside a transaction, insert with OUTPUT INTO a table variable, and assign IDs with MAX + ROW_NUMBER.
2. Run: keep the two CTEs from step 1 and replace the final SELECT with:

   , NewRows AS
   (
       SELECT  (SELECT MAX(CustomerID) FROM Lab.Customer)
                 + ROW_NUMBER() OVER (ORDER BY ImportRowID)                        AS CustomerID,
               LEFT(CleanName, CHARINDEX(N' ', CleanName + N' ') - 1)              AS FirstName,
               LTRIM(SUBSTRING(CleanName, CHARINDEX(N' ', CleanName + N' '), 100))  AS LastName,
               CleanEmail, Phone, City, Country
       FROM    Candidates
       WHERE   rn = 1
   )
   INSERT INTO Lab.Customer (CustomerID, FirstName, LastName, Email, Phone, City, Country,
                             CustomerSegment, DateJoined, IsActive)
   OUTPUT inserted.CustomerID, inserted.FirstName, inserted.LastName, inserted.Email
   SELECT CustomerID, FirstName, LastName, CleanEmail, Phone, City, Country,
          'Consumer', '2026-01-02', 1
   FROM   NewRows;

   Put BEGIN TRANSACTION; on a line before the WITH, and add SELECT @@ROWCOUNT AS Inserted; after the statement.
3. Observe: OUTPUT shows 41 Daisy Holt, 42 Kofi Mensah, 43 Ines Moreau, 44 Emma Wilson; Inserted = 4.
4. Why it matters: The insert’s SELECT is exactly the previewed query. MAX + ROW_NUMBER is fine here because only you are loading; in a busy system you would use an identity column or a sequence (Day 26), since two sessions could read the same MAX.
5. Confirm: SELECT COUNT(*) FROM Lab.Customer; → 44 (still inside the transaction).

Step 3 — Update changed phone numbers

1. Do: In the same transaction, update existing customers whose import phone differs.
2. Run:

   UPDATE c
   SET    c.Phone = i.Phone
   OUTPUT inserted.CustomerID, deleted.Phone AS OldPhone, inserted.Phone AS NewPhone
   FROM   Lab.Customer           AS c
   JOIN   Staging.CustomerImport AS i ON c.Email = TRIM(i.Email)
   WHERE  i.Phone IS NOT NULL
     AND  (c.Phone <> i.Phone OR c.Phone IS NULL);

3. Observe: 1 row — customer 1, +44 20 7946 0101 → +44 20 7946 0999.
4. Why it matters: Ben Carter’s import row has a different country spelling but the same phone, and Mohammed Rahman’s is identical, so neither is touched. The OR c.Phone IS NULL handles customers who had no phone (Day 1’s NULL logic).
5. Confirm: 1 row output.

Step 4 — Verify and commit

1. Do: Check the end state, then commit.
2. Run:

   SELECT  COUNT(*) AS Customers,
           SUM(CASE WHEN DateJoined = '2026-01-02' THEN 1 ELSE 0 END) AS LoadedToday,
           @@TRANCOUNT AS OpenTransactions
   FROM    Lab.Customer;
   
   COMMIT TRANSACTION;
   
   INSERT INTO Lab.ImportBatch (SourceTable, RowsLoaded)
   OUTPUT inserted.BatchID
   VALUES (N'Staging.CustomerImport', 5);   -- 4 inserted + 1 updated

3. Observe: 44, 4, 1; then the commit; then a new batch ID.
4. Why it matters: The two changes were committed together — either both or neither.
5. Confirm: SELECT @@TRANCOUNT; → 0.

Step 5 — Spot the data-quality issue

1. Do: Compare the new customers with existing ones by phone.
2. Run:

   SELECT n.CustomerID AS NewID, n.FirstName, n.LastName, o.CustomerID AS ExistingID, o.Email AS ExistingEmail
   FROM   Lab.Customer AS n
   JOIN   Lab.Customer AS o ON o.Phone = n.Phone AND o.CustomerID <> n.CustomerID
   WHERE  n.CustomerID > 40;

3. Observe: 1 row — new customer 44 (Emma Wilson) has the same phone as existing customer 5, who has no email.
4. Why it matters: Email was the only match key, so a likely duplicate person was created. Day 30’s load process matches on several attributes and routes uncertain matches for review.
5. Confirm: 1 row.

F. Independent practice

Recreate the Lab copies (section C) before starting if you want a clean state.

Task 1 — Copy rows with INSERT … SELECT

Create Lab.CancelledOrderReview (OrderID int PRIMARY KEY, CustomerID int, OrderDate date, Notes nvarchar(400), ReviewStatus varchar(10) DEFAULT ('Open')) and insert every 2025 cancelled order. Use OUTPUT to return the inserted order IDs.

Success criteria: 4 rows — 1255, 1273, 1301, 1344 — all with ReviewStatus = 'Open'.

Task 2 — Promote high-value consumers (UPDATE with a join)

In Lab.Customer, change CustomerSegment from Consumer to VIP for consumers whose lifetime net sales (non-cancelled) are at least 4,000. Preview first, run in a transaction, return old and new segments with OUTPUT, check @@ROWCOUNT, then commit.

Success criteria: 2 customers — 8 (4684.21) and 24 (4690.98).

Task 3 — Archive and remove inactive non-buyers (DELETE … OUTPUT INTO)

Create Lab.CustomerArchive with the same columns as Lab.Customer plus ArchivedAt datetime2(0). In one statement, delete inactive customers who have never ordered from Lab.Customer and write them to the archive.

Success criteria: 1 customer archived — 36 (Mia Harper).

Task 4 — The import load as a MERGE (challenging)

Starting from a fresh Lab.Customer copy, write one MERGE that inserts the four new customers and updates Alice Morgan’s phone, with OUTPUT $action. Then compare the result with the lab’s two-statement approach using EXCEPT — they must be identical.

Success criteria: $action counts: INSERT 4, UPDATE 1. An EXCEPT between the MERGE result and the lab result (both directions, excluding nothing) returns 0 rows.

Stretch challenge — Delete in batches

Copy Sales.Payment to Lab.Payment. Delete all payments dated before 2025 in batches of 50, archiving each batch with OUTPUT INTO Lab.PaymentArchive, and print the size of each batch. Use a WHILE loop that stops when a batch deletes no rows.

Success criteria: batches of 50, 50, 50 and 34 (184 rows); Lab.Payment keeps 211 rows; Lab.PaymentArchive has 184.

DECLARE @rows int = 1;
WHILE @rows > 0
BEGIN
    DELETE TOP (50) FROM … OUTPUT … INTO … WHERE …;
    SET @rows = @@ROWCOUNT;
    PRINT CONCAT('Deleted ', @rows);
END;

G. Solutions and explanations

DROP TABLE IF EXISTS Lab.CancelledOrderReview;
CREATE TABLE Lab.CancelledOrderReview
(
    OrderID      int           NOT NULL PRIMARY KEY,
    CustomerID   int           NOT NULL,
    OrderDate    date          NOT NULL,
    Notes        nvarchar(400) NULL,
    ReviewStatus varchar(10)   NOT NULL DEFAULT ('Open')
);

INSERT INTO Lab.CancelledOrderReview (OrderID, CustomerID, OrderDate, Notes)
OUTPUT inserted.OrderID, inserted.ReviewStatus
SELECT OrderID, CustomerID, OrderDate, Notes
FROM   Sales.SalesOrder
WHERE  OrderStatus = 'Cancelled'
  AND  OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01';

-- Preview
WITH CustomerNet AS
(
    SELECT  o.CustomerID, SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS LifetimeNet
    FROM    Sales.SalesOrder AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
    GROUP BY o.CustomerID
)
SELECT c.CustomerID, c.CustomerSegment, CAST(n.LifetimeNet AS decimal(12,2)) AS LifetimeNet
FROM   Lab.Customer AS c JOIN CustomerNet AS n ON n.CustomerID = c.CustomerID
WHERE  c.CustomerSegment = 'Consumer' AND n.LifetimeNet >= 4000;

-- Change
BEGIN TRANSACTION;

WITH CustomerNet AS
(
    SELECT  o.CustomerID, SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS LifetimeNet
    FROM    Sales.SalesOrder AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
    GROUP BY o.CustomerID
)
UPDATE c
SET    c.CustomerSegment = 'VIP'
OUTPUT inserted.CustomerID, deleted.CustomerSegment AS OldSegment, inserted.CustomerSegment AS NewSegment
FROM   Lab.Customer AS c
JOIN   CustomerNet  AS n ON n.CustomerID = c.CustomerID
WHERE  c.CustomerSegment = 'Consumer' AND n.LifetimeNet >= 4000;

SELECT @@ROWCOUNT AS RowsUpdated;   -- 2

COMMIT TRANSACTION;

DROP TABLE IF EXISTS Lab.CustomerArchive;
SELECT TOP (0) * INTO Lab.CustomerArchive FROM Lab.Customer;
ALTER TABLE Lab.CustomerArchive ADD ArchivedAt datetime2(0) NULL;

BEGIN TRANSACTION;

DELETE c
OUTPUT deleted.CustomerID, deleted.FirstName, deleted.LastName, deleted.Email, deleted.Phone,
       deleted.City, deleted.Country, deleted.CustomerSegment, deleted.DateJoined, deleted.IsActive,
       SYSUTCDATETIME()
INTO   Lab.CustomerArchive (CustomerID, FirstName, LastName, Email, Phone, City, Country,
                            CustomerSegment, DateJoined, IsActive, ArchivedAt)
FROM   Lab.Customer AS c
WHERE  c.IsActive = 0
  AND  NOT EXISTS (SELECT 1 FROM Sales.SalesOrder AS o WHERE o.CustomerID = c.CustomerID);

SELECT CustomerID, FirstName, LastName FROM Lab.CustomerArchive;

COMMIT TRANSACTION;

-- Assumes a fresh Lab.Customer (re-run the Lab.Customer part of the setup)
WITH Cleaned AS
(
    SELECT  i.ImportRowID,
            NULLIF(REPLACE(REPLACE(REPLACE(TRIM(i.FullName), N' ', N'<>'), N'><', N''), N'<>', N' '), N'') AS CleanName,
            LOWER(TRIM(i.Email)) AS CleanEmail,
            i.Phone, TRIM(i.City) AS City,
            CASE WHEN UPPER(TRIM(i.Country)) IN (N'UK', N'UNITED KINGDOM') THEN N'United Kingdom'
                 ELSE TRIM(i.Country) END AS Country
    FROM    Staging.CustomerImport AS i
),
OnePerEmail AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CleanEmail ORDER BY ImportRowID) AS rn
    FROM   Cleaned
    WHERE  CleanName IS NOT NULL
      AND  REGEXP_LIKE(CleanEmail, '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$')
),
Source AS
(
    SELECT  o.*,
            (SELECT MAX(CustomerID) FROM Lab.Customer)
              + ROW_NUMBER() OVER (ORDER BY CASE WHEN c.CustomerID IS NULL THEN 0 ELSE 1 END, o.ImportRowID) AS NewID
    FROM    OnePerEmail AS o
    LEFT JOIN Lab.Customer AS c ON c.Email = o.CleanEmail
    WHERE   o.rn = 1
)
MERGE Lab.Customer AS t
USING Source AS s
   ON t.Email = s.CleanEmail
WHEN MATCHED AND s.Phone IS NOT NULL AND (t.Phone <> s.Phone OR t.Phone IS NULL) THEN
     UPDATE SET t.Phone = s.Phone
WHEN NOT MATCHED BY TARGET THEN
     INSERT (CustomerID, FirstName, LastName, Email, Phone, City, Country, CustomerSegment, DateJoined, IsActive)
     VALUES (s.NewID,
             LEFT(s.CleanName, CHARINDEX(N' ', s.CleanName + N' ') - 1),
             LTRIM(SUBSTRING(s.CleanName, CHARINDEX(N' ', s.CleanName + N' '), 100)),
             s.CleanEmail, s.Phone, s.City, s.Country, 'Consumer', '2026-01-02', 1)
OUTPUT $action, inserted.CustomerID, inserted.Email;

DROP TABLE IF EXISTS Lab.Payment, Lab.PaymentArchive;
SELECT * INTO Lab.Payment FROM Sales.Payment;
SELECT TOP (0) * INTO Lab.PaymentArchive FROM Sales.Payment;
GO

DECLARE @rows int = 1, @batch int = 0;

WHILE @rows > 0
BEGIN
    DELETE TOP (50) FROM Lab.Payment
    OUTPUT deleted.PaymentID, deleted.OrderID, deleted.PaymentDate, deleted.Amount, deleted.PaymentMethod
    INTO   Lab.PaymentArchive (PaymentID, OrderID, PaymentDate, Amount, PaymentMethod)
    WHERE  PaymentDate < '2025-01-01';

    SET @rows = @@ROWCOUNT;
    SET @batch += 1;
    PRINT CONCAT('Batch ', @batch, ': deleted ', @rows, ' rows');
END;

SELECT (SELECT COUNT(*) FROM Lab.Payment) AS Remaining, (SELECT COUNT(*) FROM Lab.PaymentArchive) AS Archived;

H. Knowledge check

1. Understanding. In an UPDATE, what do the inserted and deleted tables contain?

2. Debugging. After INSERT INTO t (…) SELECT … FROM big_source; (500 rows), a developer uses SCOPE_IDENTITY() to find the new IDs. What’s wrong?

3. Query interpretation. A MERGE has ON t.ProductCode = s.ProductCode AND t.DiscontinuedDate IS NULL and a WHEN NOT MATCHED BY TARGET THEN INSERT branch. What happens to a source row for a discontinued product?

4. Debugging. A MERGE fails with error 8672. What is the cause, and the fix?

5. Understanding. Inside a transaction you insert error details into a table variable, then roll back. Are the details still there? What if you had used a temporary table?

I. Troubleshooting and cleanup

Cleanup. Today’s work touched only Lab copies. Leave them or remove them — nothing later depends on them:

DROP TABLE IF EXISTS Lab.Product, Lab.Customer, Lab.StockSummary, Lab.ProductArchive, Lab.CustomerImportCopy,
                     Lab.ImportBatch, Lab.CancelledOrderReview, Lab.CustomerArchive, Lab.CustomerAfterLab,
                     Lab.Payment, Lab.PaymentArchive;
SELECT @@TRANCOUNT AS OpenTransactions;   -- must be 0

Do not drop the Day 12 objects (Lab.Calendar, Lab.ProductListPrice, Lab.ProductListPriceHistory). Core tables are unchanged.

J. Completion checklist, recap and references

Completion checklist

• You followed preview → transaction → @@ROWCOUNT → verify → commit/rollback for every change.
• Worked example 2’s audit showed 5 price changes; worked example 5’s MERGE showed 2 updates, 1 insert, 1 delete.
• You saw error 8672 and can explain it.
• Your lab load produced 44 customers (4 new, 1 updated) and found the Emma Wilson duplicate.
• Tasks 1–4 match the success criteria; @@TRANCOUNT is 0 at the end.

Recap

• Preview with the same FROM/WHERE, change inside a transaction, check @@ROWCOUNT immediately, verify, then commit or roll back.
• List target columns in INSERT; use OUTPUT inserted.… to capture identity values for multi-row inserts.
• UPDATE/DELETE can use joins and CTEs; de-duplicate sources so each target row has one match.
• OUTPUT exposes inserted/deleted (and $action for MERGE) for auditing and chaining.
• MERGE: keys only in ON, one source row per target row, careful with NOT MATCHED BY SOURCE, HOLDLOCK for concurrent upserts — or use separate statements in one transaction.
• Temporary tables have statistics and roll back; table variables are batch-scoped and survive rollbacks.

Next: Day 16 — Transactions and concurrency: isolation levels, locking, blocking and deadlocks, with two-session labs.

References (Microsoft Learn)

• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• DELETE (Transact-SQL)
• OUTPUT clause (Transact-SQL)
• MERGE (Transact-SQL) — including the remarks on ON, HOLDLOCK and performance
• SCOPE_IDENTITY (Transact-SQL)
• table (Transact-SQL) — table variables
• SELECT – INTO clause (Transact-SQL)