SQL Syllabus

Week 2 · Reporting SQL / Day 9

Day 9: INSERT, UPDATE, DELETE, MERGE, transactions

Fri 9 October 2026 · 24 queries · run on AWPractice (a practice copy)

Goal: change data without fear, because you always know how to undo it.

Read: W3Schools SQL Insert Into, SQL Update, SQL Delete, SQL Insert Into Select. Microsoft Learn: "MERGE (Transact-SQL)" and "Transactions".

Where this runs: everything below runs in a practice database, AWPractice, built from copies of the AdventureWorks tables, so AdventureWorks2022 itself is never changed. The steps run once, in order, top to bottom: each one depends on the state the previous one left behind.

On this page

  1. Setup 2
  2. Drill 13
  3. Stretch 4
  4. Self-test 5

Setup

#1

Build the practice copy

CREATE DATABASE AWPractice;
GO
USE AWPractice;
GO
SELECT * INTO Product FROM AdventureWorks2022.Production.Product;
SELECT * INTO SalesOrderHeader FROM AdventureWorks2022.Sales.SalesOrderHeader;
SELECT * INTO SalesOrderDetail FROM AdventureWorks2022.Sales.SalesOrderDetail;
SELECT * INTO Territory FROM AdventureWorks2022.Sales.SalesTerritory;

What this query does

CREATE DATABASE makes a new, empty database so all of today's changes happen away from AdventureWorks2022. GO ends a batch: SQL Server must finish creating the database before USE can switch to it. SELECT * INTO creates each table and copies its rows in one step. The Messages tab shows the rows copied: 504 products, 31,465 orders, 121,317 order lines and 10 territories.

Result in SSMS: Messages tab: 504, 31,465, 121,317 and 10 rows copied
#2

Build the practice copy (continued)

-- What did SELECT INTO really copy? Check for identity columns.
SELECT t.name AS TableName, c.name AS IdentityColumn
FROM sys.identity_columns c
JOIN sys.tables t ON c.object_id = t.object_id;

What this query does

The plan says SELECT INTO copies no keys, indexes or constraints, which is true. But it does copy the IDENTITY property: all four tables still generate their own IDs automatically. sys.identity_columns is a system view listing every identity column. This explains the errors in drills 1 and 3 below.

SELECT INTO does copy the IDENTITY property. That breaks the plan's inserts with explicit IDs (drills 1-3, MERGE).

Result in SSMS: 4 rows: all four tables kept an identity column

Drill

#3

1. Insert one row. Count before and after.

SELECT COUNT(*) AS Before FROM Territory;
INSERT INTO Territory (TerritoryID, Name, CountryRegionCode, [Group], SalesYTD, SalesLastYear,
                       CostYTD, CostLastYear, rowguid, ModifiedDate)
VALUES (11, 'Pakistan', 'PK', 'Asia', 0, 0, 0, 0, NEWID(), GETDATE());
SELECT COUNT(*) AS After FROM Territory;

What this query does

The plan's version. The INSERT fails with error 544: you cannot type your own value into an identity column while IDENTITY_INSERT is OFF. The count before and after is 10 both times, so the count check did its job and proved nothing was inserted.

Result in SSMS: error 544, the INSERT failed; Before = 10 and After = 10
#4

1. Insert one row. Count before and after. (continued)

SET IDENTITY_INSERT Territory ON;
INSERT INTO Territory (TerritoryID, Name, CountryRegionCode, [Group], SalesYTD, SalesLastYear,
                       CostYTD, CostLastYear, rowguid, ModifiedDate)
VALUES (11, 'Pakistan', 'PK', 'Asia', 0, 0, 0, 0, NEWID(), GETDATE());
SET IDENTITY_INSERT Territory OFF;
SELECT COUNT(*) AS After FROM Territory;

What this query does

SET IDENTITY_INSERT Territory ON allows an explicit TerritoryID for this session, and it needs a column list, which the INSERT already has. Only one table per session can have it ON, so switch it OFF straight away. The count is now 11. [Group] has square brackets because GROUP is a reserved word.

SET IDENTITY_INSERT ON lets you supply the ID yourself. Always switch it OFF again straight after.

Result in SSMS: Territory now has 11 rows
#5

2. Insert several rows in one statement

SET IDENTITY_INSERT Territory ON;
INSERT INTO Territory (TerritoryID, Name, CountryRegionCode, [Group], SalesYTD, SalesLastYear,
                       CostYTD, CostLastYear, rowguid, ModifiedDate)
VALUES (12, 'India', 'IN', 'Asia', 0, 0, 0, 0, NEWID(), GETDATE()),
       (13, 'UAE', 'AE', 'Middle East', 0, 0, 0, 0, NEWID(), GETDATE());
SET IDENTITY_INSERT Territory OFF;
SELECT TerritoryID, Name, CountryRegionCode, [Group] FROM Territory WHERE TerritoryID >= 11;

What this query does

One VALUES clause can hold many rows separated by commas, inserted in one statement, which is faster than separate INSERTs and all-or-nothing. The SELECT afterwards shows the three new territories.

Result in SSMS: 3 rows: Pakistan, India and UAE
#6

3. INSERT from a SELECT. Copy the 2014 orders into an archive table.

SELECT * INTO SalesOrderHeader2014 FROM SalesOrderHeader WHERE 1 = 0;   -- empty copy of the structure
INSERT INTO SalesOrderHeader2014 SELECT * FROM SalesOrderHeader WHERE YEAR(OrderDate) = 2014;
SELECT COUNT(*) AS Archived FROM SalesOrderHeader2014;

What this query does

The plan's version. WHERE 1 = 0 copies the structure with no rows (Day 2), including the identity on SalesOrderID. The INSERT ... SELECT * then fails with error 8101: an explicit value for an identity column needs a column list and IDENTITY_INSERT ON. The count is 0.

Result in SSMS: error 8101, nothing archived
#7

3. INSERT from a SELECT. Copy the 2014 orders into an archive table. (continued)

DROP TABLE SalesOrderHeader2014;
-- UNION ALL makes SELECT INTO drop the IDENTITY property
SELECT * INTO SalesOrderHeader2014 FROM SalesOrderHeader WHERE 1 = 0
UNION ALL
SELECT * FROM SalesOrderHeader WHERE 1 = 0;
-- new batch, so the INSERT is compiled against the new table
GO
INSERT INTO SalesOrderHeader2014 SELECT * FROM SalesOrderHeader WHERE YEAR(OrderDate) = 2014;
SELECT COUNT(*) AS Archived FROM SalesOrderHeader2014;

What this query does

An archive should store the original SalesOrderID, so it should not have an identity. A known trick: when SELECT INTO reads from a UNION ALL, SQL Server does not copy the IDENTITY property. GO starts a new batch: without it, SQL Server compiles the whole batch while the old table (with its identity) still exists, and the INSERT fails with the same error. Now INSERT ... SELECT * works and archives the 11,761 orders from 2014.

An archive table should keep the original order IDs, not make new ones, so it should not have an identity at all.

Result in SSMS: 11,761 orders from 2014 archived
#8

4. UPDATE one row. Always SELECT with the same WHERE first.

SELECT ProductID, Name, ListPrice FROM Product WHERE ProductID = 776;
UPDATE Product SET ListPrice = ListPrice * 1.10 WHERE ProductID = 776;
SELECT ProductID, Name, ListPrice FROM Product WHERE ProductID = 776;

What this query does

Before changing anything, SELECT with exactly the same WHERE to see which rows will be hit. The UPDATE then raises product 776's price by 10%: 3,374.99 becomes 3,712.49. The two result grids show before and after.

Habit: run a SELECT with the exact WHERE you will use, check the rows, then change SELECT to UPDATE.

Result in SSMS: before 3,374.99, after 3,712.49
#9

5. UPDATE many rows with a calculation

SELECT COUNT(*) AS RowsToChange FROM Product WHERE Color = 'Red' AND ListPrice > 0;
UPDATE Product SET ListPrice = ROUND(ListPrice * 1.05, 2) WHERE Color = 'Red' AND ListPrice > 0;

What this query does

SET can use the column's current value in a calculation: each red product gets 5% more, rounded to 2 decimals. The count first predicts 38 rows, and the Messages tab confirms '(38 rows affected)'.

Result in SSMS: 38 rows predicted, 38 rows affected
#10

6. UPDATE with a JOIN. Raise prices on Bikes only.

UPDATE p
SET p.ListPrice = p.ListPrice * 1.02
FROM Product p
JOIN AdventureWorks2022.Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
JOIN AdventureWorks2022.Production.ProductCategory c ON s.ProductCategoryID = c.ProductCategoryID
WHERE c.Name = 'Bikes';

What this query does

T-SQL lets UPDATE use a FROM with joins: write UPDATE <alias>, then a normal FROM ... JOIN ... WHERE. Here the practice Product table is joined to the category tables in AdventureWorks2022 (database.schema.table names work across databases), and only Bikes get 2% more: 97 rows.

Result in SSMS: 97 rows affected
#11

7. DELETE with a WHERE, then DELETE with no WHERE

DELETE FROM Territory WHERE TerritoryID IN (12, 13);
DELETE FROM SalesOrderHeader2014;
SELECT COUNT(*) AS ArchiveRows FROM SalesOrderHeader2014;

What this query does

The first DELETE removes just the two territories in the list. The second has no WHERE, so it removes every row of the archive (11,761) with no warning or confirmation. The count proves the table is empty, but it still exists.

A DELETE with no WHERE removes every row, with no warning. That is why the habits at the end of this page exist.

Result in SSMS: 2 territories and all 11,761 archive rows deleted; archive count 0
#12

8. TRUNCATE vs DELETE. Faster, resets identity, cannot have WHERE.

INSERT INTO SalesOrderHeader2014 SELECT * FROM SalesOrderHeader WHERE YEAR(OrderDate) = 2014;
SELECT COUNT(*) AS Refilled FROM SalesOrderHeader2014;
TRUNCATE TABLE SalesOrderHeader2014;
SELECT COUNT(*) AS AfterTruncate FROM SalesOrderHeader2014;

What this query does

TRUNCATE TABLE also empties a table, but it releases whole data pages instead of deleting row by row, so it is much faster on big tables. It cannot have a WHERE, it resets an identity back to its seed, and it is blocked on tables referenced by a foreign key. It can still be rolled back inside a transaction.

Result in SSMS: 11,761 rows refilled, 0 after TRUNCATE
#13

9. Transactions. The undo button.

BEGIN TRANSACTION;
UPDATE Product SET ListPrice = 0;
SELECT TOP 5 Name, ListPrice FROM Product ORDER BY ProductID DESC;   -- all zero
ROLLBACK TRANSACTION;
SELECT TOP 5 Name, ListPrice FROM Product ORDER BY ProductID DESC;   -- restored

What this query does

BEGIN TRANSACTION starts a unit of work. The UPDATE with no WHERE sets all 504 prices to 0, and the first SELECT shows zeros. ROLLBACK TRANSACTION undoes everything since BEGIN, so the second SELECT shows the real prices again. ORDER BY ProductID DESC is added to the plan's SELECT: without it the first 5 rows are components that cost 0 anyway, so the rollback would look like it did nothing. Other users would not have seen the zeros, because the changed rows stay locked until the transaction ends.

Inside BEGIN TRANSACTION nothing is final. ROLLBACK undoes every change since BEGIN.

Result in SSMS: prices are 0 inside the transaction, restored after ROLLBACK
#14

9. Transactions. The undo button. (continued)

BEGIN TRANSACTION;
UPDATE Product SET Color = 'Black' WHERE Color IS NULL;
SELECT COUNT(*) AS StillNull FROM Product WHERE Color IS NULL;   -- zero
COMMIT TRANSACTION;   -- now it's permanent

What this query does

This time the change is kept. The 248 products with no colour become Black, the check shows 0 NULLs left, and COMMIT makes it permanent. After COMMIT there is no undo except restoring a backup, which is why you check the count before committing.

Result in SSMS: 248 rows changed and committed; 0 NULL colours left
#15

10. OUTPUT clause. See what you changed.

UPDATE Product SET ListPrice = ListPrice + 1
OUTPUT deleted.ProductID, deleted.ListPrice AS OldPrice, inserted.ListPrice AS NewPrice
WHERE ProductID BETWEEN 770 AND 775;

What this query does

OUTPUT returns the changed rows as a result set. deleted holds the values before the change and inserted the values after, so old and new prices sit side by side. Useful as an audit trail: OUTPUT ... INTO a log table records every change.

Result in SSMS: 6 rows, old and new price side by side

Stretch

#16

MERGE. Upsert territories from a staging table.

CREATE TABLE TerritoryStaging (TerritoryID INT, Name NVARCHAR(50), CountryRegionCode NVARCHAR(3), [Group] NVARCHAR(50));
INSERT INTO TerritoryStaging VALUES (1, 'Northwest Region', 'US', 'North America'), (14, 'Bangladesh', 'BD', 'Asia');

SET IDENTITY_INSERT Territory ON;
MERGE Territory AS target
USING TerritoryStaging AS source ON target.TerritoryID = source.TerritoryID
WHEN MATCHED THEN UPDATE SET target.Name = source.Name
WHEN NOT MATCHED THEN INSERT (TerritoryID, Name, CountryRegionCode, [Group], SalesYTD, SalesLastYear,
                              CostYTD, CostLastYear, rowguid, ModifiedDate)
     VALUES (source.TerritoryID, source.Name, source.CountryRegionCode, source.[Group],
             0, 0, 0, 0, NEWID(), GETDATE())
OUTPUT $action, inserted.TerritoryID, inserted.Name;
SET IDENTITY_INSERT Territory OFF;

What this query does

MERGE compares a source (the staging table) with a target on a key. WHEN MATCHED updates existing rows; WHEN NOT MATCHED inserts new ones. This is an 'upsert', the core of loading data warehouses and what SSIS does later. OUTPUT $action shows what happened to each row: territory 1 was UPDATEd to 'Northwest Region' and 14, Bangladesh, was INSERTed. A MERGE must end with a semicolon.

Same identity issue as drill 1, so IDENTITY_INSERT is switched ON around the MERGE.

Result in SSMS: 2 rows: INSERT Bangladesh, UPDATE Northwest Region
#17

Delete duplicates keeping the lowest ID

SET IDENTITY_INSERT Territory ON;
INSERT INTO Territory (TerritoryID, Name, CountryRegionCode, [Group], SalesYTD, SalesLastYear,
                       CostYTD, CostLastYear, rowguid, ModifiedDate)
SELECT TerritoryID, Name, CountryRegionCode, [Group], SalesYTD, SalesLastYear,
       CostYTD, CostLastYear, rowguid, ModifiedDate
FROM Territory WHERE TerritoryID = 11;
SET IDENTITY_INSERT Territory OFF;
SELECT TerritoryID, Name FROM Territory WHERE TerritoryID = 11;

What this query does

Making a duplicate on purpose. There is no primary key yet (Day 10 adds one), so nothing stops the same TerritoryID going in twice; the SELECT shows Pakistan twice. The plan's INSERT ... SELECT * needs a column list here, because of the identity again.

Result in SSMS: TerritoryID 11 now appears twice
#18

Delete duplicates keeping the lowest ID (continued)

WITH Dupes AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY TerritoryID ORDER BY ModifiedDate) AS rn
    FROM Territory
)
DELETE FROM Dupes WHERE rn > 1;
SELECT TerritoryID, Name FROM Territory WHERE TerritoryID = 11;

What this query does

The Day 8 de-duplication pattern, now actually deleting: ROW_NUMBER numbers each copy within its TerritoryID, and DELETE FROM the CTE removes every row numbered 2 or higher from the real table. One Pakistan row is left.

You can DELETE through a CTE: it deletes the underlying table rows.

Result in SSMS: 1 duplicate deleted; TerritoryID 11 appears once
#19

Forget the WHERE on purpose inside a transaction

BEGIN TRANSACTION;
DELETE FROM Product;
SELECT @@ROWCOUNT AS RowsDeleted;
ROLLBACK TRANSACTION;
SELECT COUNT(*) AS ProductsAfterRollback FROM Product;

What this query does

The classic disaster, made safe. DELETE with no WHERE removes all 504 products; @@ROWCOUNT (the number of rows the last statement affected) shows 504, far more than you meant. ROLLBACK brings them all back, and the final count is 504 again. Inside a transaction a mistake is just a number to read.

Result in SSMS: 504 rows deleted, 504 back after ROLLBACK

Self-test

#20

Q1. Insert three products with distinct IDs above 1000

SET IDENTITY_INSERT Product ON;
INSERT INTO Product (ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag, SafetyStockLevel,
                     ReorderPoint, StandardCost, ListPrice, DaysToManufacture, SellStartDate,
                     rowguid, ModifiedDate)
VALUES (1001, 'Practice Bottle', 'PR-1001', 0, 1, 100, 75, 2.00, 5.99, 0, GETDATE(), NEWID(), GETDATE()),
       (1002, 'Practice Gloves', 'PR-1002', 0, 1, 100, 75, 8.00, 19.99, 0, GETDATE(), NEWID(), GETDATE()),
       (1003, 'Practice Lock', 'PR-1003', 0, 1, 100, 75, 10.00, 24.99, 0, GETDATE(), NEWID(), GETDATE());
SET IDENTITY_INSERT Product OFF;
SELECT ProductID, Name, ListPrice FROM Product WHERE ProductID > 1000;

What this query does

Every column that does not allow NULL must get a value, and SELECT INTO did not copy the defaults the original table had, so all 13 required columns are listed. IDENTITY_INSERT is needed again because ProductID is an identity. The check shows the three new products.

Result in SSMS: 3 new products
#21

Q2. Raise ListPrice 8 percent on everything in subcategory 1

SELECT COUNT(*) AS RowsToChange FROM Product WHERE ProductSubcategoryID = 1;
UPDATE Product SET ListPrice = ROUND(ListPrice * 1.08, 2) WHERE ProductSubcategoryID = 1;

What this query does

Count first with the same WHERE (32 mountain bikes), then update. The Messages tab confirms 32 rows affected, matching the prediction.

Result in SSMS: 32 rows predicted, 32 rows affected
#22

Q3. Delete orders from 2011 inside a transaction, check the count, roll back

BEGIN TRANSACTION;
DELETE FROM SalesOrderHeader WHERE OrderDate < '2012-01-01';
SELECT @@ROWCOUNT AS RowsDeleted;
SELECT COUNT(*) AS Orders2011Left FROM SalesOrderHeader WHERE OrderDate < '2012-01-01';
ROLLBACK TRANSACTION;
SELECT COUNT(*) AS Orders2011AfterRollback FROM SalesOrderHeader WHERE OrderDate < '2012-01-01';

What this query does

The 1,607 orders from 2011 are deleted inside a transaction: @@ROWCOUNT reports 1,607 and the check finds 0 left. ROLLBACK restores them, and the final count is 1,607 again. The date range uses < '2012-01-01' rather than YEAR() so it would also work well with an index.

Result in SSMS: 1,607 deleted, 0 left, 1,607 again after ROLLBACK
#23

Q4. Archive all Shipped orders before 2012 into a new table with INSERT SELECT

SELECT * INTO ShippedArchivePre2012 FROM SalesOrderHeader WHERE 1 = 0
UNION ALL
SELECT * FROM SalesOrderHeader WHERE 1 = 0;
INSERT INTO ShippedArchivePre2012
SELECT * FROM SalesOrderHeader WHERE Status = 5 AND OrderDate < '2012-01-01';
SELECT COUNT(*) AS Archived FROM ShippedArchivePre2012;

What this query does

Status 5 means Shipped (table map, Day 7). The empty archive is created with the UNION ALL trick from drill 3 so it keeps the original order IDs, then INSERT ... SELECT copies the 1,607 shipped orders from before 2012. Every 2011 order is shipped, so this matches Q3.

Result in SSMS: 1,607 shipped orders archived
#24

Q5. MERGE a staging table of two products, one existing and one new

CREATE TABLE ProductStaging (ProductID INT, Name NVARCHAR(50), ListPrice MONEY);
INSERT INTO ProductStaging VALUES (776, 'Mountain-100 Black, 42', 2299.00), (1004, 'Practice Pump', 29.99);

SET IDENTITY_INSERT Product ON;
MERGE Product AS target
USING ProductStaging AS source ON target.ProductID = source.ProductID
WHEN MATCHED THEN UPDATE SET target.ListPrice = source.ListPrice, target.ModifiedDate = GETDATE()
WHEN NOT MATCHED THEN INSERT (ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag, SafetyStockLevel,
                              ReorderPoint, StandardCost, ListPrice, DaysToManufacture, SellStartDate,
                              rowguid, ModifiedDate)
     VALUES (source.ProductID, source.Name, 'PR-' + CAST(source.ProductID AS VARCHAR(10)), 0, 1, 100, 75,
             0, source.ListPrice, 0, GETDATE(), NEWID(), GETDATE())
OUTPUT $action, inserted.ProductID, inserted.Name, deleted.ListPrice AS OldPrice, inserted.ListPrice AS NewPrice;
SET IDENTITY_INSERT Product OFF;

What this query does

Product 776 already exists, so MERGE takes the WHEN MATCHED branch and sets its price to 2,299.00 (after today's raises it had reached 4,089.68); 1004 is new, so it is inserted with every required column filled in. OUTPUT shows UPDATE and INSERT, and for the update deleted.ListPrice gives the old price beside the new one.

Result in SSMS: 2 rows: INSERT product 1004, UPDATE product 776
Habits that keep you employed: write the WHERE before the SET; run a SELECT with the same WHERE before any UPDATE or DELETE; wrap anything over 100 rows in a transaction and check the count before COMMIT; never run a bare DELETE or UPDATE on a production connection.
Not run: the plan's last step, USE master; DROP DATABASE AWPractice;, rebuilds the practice copy from scratch when it gets messy. AWPractice is kept for Day 10, which adds keys and constraints to it.