Chapter 9: Data Modification and Schema Design

All queries so far have been read-only. This chapter covers the other side: writing data with INSERT, UPDATE, DELETE, and MERGE, and designing the tables that hold it with CREATE TABLE, constraints, and schema organization.

9.1 INSERT

INSERT adds one or more rows to a table.

Single Row

INSERT INTO production.categories (category_name)
VALUES ('Electric Scooters');

The IDENTITY column (category_id) is auto-generated. Only supply values for columns that are not identity or have no default.

Multiple Rows

INSERT INTO production.brands (brand_name)
VALUES
    ('Cannondale'),
    ('Giant'),
    ('Scott');

INSERT INTO SELECT

Copies rows from a query result into a table. INSERT never creates the target, so the table has to exist first. Create the archive before copying anything into it:

CREATE TABLE production.products_archive (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR (255) NOT NULL,
    brand_id     INT NOT NULL,
    category_id  INT NOT NULL,
    model_year   SMALLINT NOT NULL,
    list_price   DECIMAL (10, 2) NOT NULL
);

The archive mirrors production.products with two deliberate differences. product_id is a plain INT rather than an IDENTITY column, because the archive keeps the ids the products already have instead of generating new ones. And there are no foreign keys, so an archived row survives even if its brand or category is later deleted.

Now the copy itself:

INSERT INTO production.products_archive (
    product_id, product_name, brand_id, category_id, model_year, list_price
)
SELECT product_id, product_name, brand_id, category_id, model_year, list_price
FROM production.products
WHERE model_year < 2017;

The columns and data types of the SELECT must line up with the column list of the target table, in the same order.

⚠ Common Mistake - Omitting the column list works only when inserting values for every column in the table, in the exact column order. Always specify the column list to guard against schema changes.

9.2 UPDATE

UPDATE changes existing rows. Always include a WHERE clause unless you intend to update every row.

UPDATE production.products
SET list_price = list_price * 1.05
WHERE category_id = 6
  AND model_year = 2018;

Multiple columns can be updated in a single statement:

UPDATE sales.customers
SET
    phone = '(916) 555-0199',
    city  = 'Sacramento'
WHERE customer_id = 142;

UPDATE with JOIN

Updates a table based on values from another table:

UPDATE p
SET p.list_price = p.list_price * 1.10
FROM production.products AS p
INNER JOIN production.categories AS c
    ON p.category_id = c.category_id
WHERE c.category_name = 'Electric Bikes';

⚠ Common Mistake - A UPDATE without a WHERE clause updates every row in the table. Always write and test the WHERE clause as a SELECT first to confirm what rows will be affected.

9.3 DELETE

DELETE removes rows from a table.

DELETE FROM production.products
WHERE model_year < 2016;

DELETE with JOIN

DELETE p
FROM production.products AS p
INNER JOIN production.categories AS c
    ON p.category_id = c.category_id
WHERE c.category_name = 'Electric Scooters';

TRUNCATE TABLE

TRUNCATE TABLE removes all rows faster than DELETE because it does not log individual row deletions. It cannot be used with a WHERE clause and cannot be rolled back without a transaction.

TRUNCATE TABLE production.products_archive;

	DELETE	TRUNCATE
WHERE clause	Yes	No
Transaction log	Row by row	Minimal
Triggers fired	Yes	No
Resets IDENTITY	No	Yes
Rollback	Yes	Only inside transaction

9.4 MERGE

MERGE combines INSERT, UPDATE, and DELETE into a single statement. It compares a source table against a target table and performs the appropriate action on each row.

The source here is a staging table: a plain table that an ETL job fills with the latest extract from the source system before the load runs. Create it first:

CREATE TABLE production.products_staging (
    product_id   INT NULL,
    product_name VARCHAR (255) NOT NULL,
    brand_id     INT NOT NULL,
    category_id  INT NOT NULL,
    model_year   SMALLINT NOT NULL,
    list_price   DECIMAL (10, 2) NOT NULL
);

It holds the same columns as production.products, with product_id nullable and no IDENTITY. A product that already exists arrives carrying its id, which is what the MERGE joins on. A brand-new product arrives with no id at all, because the id is generated when it is inserted into production.products.

With the staging table loaded, one statement reconciles the two:

MERGE production.products AS target
USING production.products_staging AS source
    ON target.product_id = source.product_id
WHEN MATCHED AND (
    target.list_price <> source.list_price
    OR target.model_year <> source.model_year
)
    THEN UPDATE SET
        target.list_price = source.list_price,
        target.model_year = source.model_year
WHEN NOT MATCHED BY TARGET
    THEN INSERT (product_name, brand_id, category_id, model_year, list_price)
    VALUES (source.product_name, source.brand_id, source.category_id,
            source.model_year, source.list_price)
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

MERGE logic:

  Source row MATCHES target row     -> UPDATE if values differ
  Source row NOT IN target           -> INSERT into target
  Target row NOT IN source           -> DELETE from target

MERGE is the standard pattern for loading data from a staging table into a production table, updating existing records, inserting new ones, and deleting stale ones in a single atomic operation.

Interview Angle "How does MERGE work and when would you use it?" MERGE compares a source to a target on a join condition and handles three scenarios: matched rows (update), rows in source but not target (insert), and rows in target but not source (delete). It is the standard upsert pattern for ETL loads, SCD-2 dimension updates, and maintaining reference tables.

9.5 Transactions

A transaction groups multiple statements so they all succeed or all fail together. This prevents partial updates from corrupting data.

BEGIN TRANSACTION;

UPDATE sales.orders
SET order_status = 4
WHERE order_id = 1;

UPDATE production.stocks
SET quantity = quantity - 1
WHERE product_id = 6 AND store_id = 1;

COMMIT;

If either statement fails, roll back to undo both:

BEGIN TRANSACTION;

BEGIN TRY
    UPDATE sales.orders SET order_status = 4 WHERE order_id = 1;
    UPDATE production.stocks SET quantity = quantity - 1 WHERE product_id = 6 AND store_id = 1;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    THROW;
END CATCH;

Statement	Effect
`BEGIN TRANSACTION`	Starts the transaction
`COMMIT`	Saves all changes permanently
`ROLLBACK`	Undoes all changes back to the `BEGIN TRANSACTION`

9.6 CREATE TABLE

A table is the fundamental storage object in SQL Server. Tables are uniquely identified by their schema and name.

CREATE TABLE [database_name.][schema_name.]table_name (
    column_1 data_type constraints,
    column_2 data_type constraints,
    ...
    table_constraints
);

Creating the BikeStores tables

CREATE TABLE production.categories (
    category_id   INT IDENTITY (1, 1) PRIMARY KEY,
    category_name VARCHAR (255) NOT NULL
);

CREATE TABLE production.brands (
    brand_id   INT IDENTITY (1, 1) PRIMARY KEY,
    brand_name VARCHAR (255) NOT NULL
);

CREATE TABLE production.products (
    product_id   INT IDENTITY (1, 1) PRIMARY KEY,
    product_name VARCHAR (255) NOT NULL,
    brand_id     INT NOT NULL,
    category_id  INT NOT NULL,
    model_year   SMALLINT NOT NULL,
    list_price   DECIMAL (10, 2) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES production.categories (category_id),
    FOREIGN KEY (brand_id) REFERENCES production.brands (brand_id)
);

IDENTITY(1,1) creates an auto-incrementing integer starting at 1, incrementing by 1. The foreign key constraints ensure category_id and brand_id must match existing rows in their parent tables.

9.7 Common Data Types

Category	Type	Use
Integer	`INT`, `BIGINT`, `SMALLINT`	Whole numbers, IDs, counts
Decimal	`DECIMAL(p,s)`, `NUMERIC(p,s)`	Exact decimals (prices, rates)
Float	`FLOAT`, `REAL`	Approximate decimals (scientific data)
String	`VARCHAR(n)`	Variable-length text
Unicode	`NVARCHAR(n)`	Multi-language text
Fixed	`CHAR(n)`	Fixed-length text (codes)
Date/Time	`DATE`, `TIME`, `DATETIME2`	Dates and times
Boolean	`BIT`	0/1 flags

DECIMAL(10, 2) means up to 10 total digits, 2 after the decimal point. Use DECIMAL for money, not FLOAT (floats have rounding errors).

9.8 Constraints

Constraints enforce rules on the data a column accepts.

PRIMARY KEY

Uniquely identifies each row. Only one per table. Cannot be NULL.

product_id INT IDENTITY(1,1) PRIMARY KEY

FOREIGN KEY

Enforces the relationship between two tables. The value must exist in the referenced table.

FOREIGN KEY (brand_id) REFERENCES production.brands (brand_id)

NOT NULL

Rejects NULL values.

product_name VARCHAR(255) NOT NULL

UNIQUE

Allows NULLs but ensures all non-NULL values are distinct.

email VARCHAR(255) UNIQUE

CHECK

Validates values against a condition.

list_price DECIMAL(10,2) CHECK (list_price >= 0)

DEFAULT

Provides a value when none is supplied.

created_at DATETIME2 DEFAULT GETDATE()

Constraint Overview

Column level:    defined inline with the column
Table level:     defined after all columns (required for multi-column constraints)

Examples:
  -- Column level
  order_status TINYINT NOT NULL CHECK (order_status BETWEEN 1 AND 4)

  -- Table level (composite key)
  CONSTRAINT pk_order_items PRIMARY KEY (order_id, item_id)

9.9 ALTER TABLE

Modifies an existing table without dropping and recreating it.

-- Add a column
ALTER TABLE sales.customers
ADD loyalty_points INT DEFAULT 0;

-- Modify a column
ALTER TABLE sales.customers
ALTER COLUMN phone VARCHAR(50);

-- Drop a column
ALTER TABLE sales.customers
DROP COLUMN loyalty_points;

-- Add a constraint
ALTER TABLE production.products
ADD CONSTRAINT chk_price CHECK (list_price > 0);

9.10 Schemas

A schema is a namespace that groups related database objects. The BikeStores database uses two: production for product data and sales for order data.

-- Create a schema
CREATE SCHEMA reporting;

-- Create a table inside it
CREATE TABLE reporting.daily_summary (
    summary_id   INT IDENTITY PRIMARY KEY,
    summary_date DATE NOT NULL,
    total_orders INT,
    total_revenue DECIMAL(15, 2)
);

Schemas serve three purposes: organizing objects logically, applying permissions at the schema level instead of per object, and avoiding naming conflicts between tables in different business domains.

BikeStores schema design:

  production.  ->  brands, categories, products, stocks
  sales.       ->  customers, orders, order_items, staffs, stores
  reporting.   ->  (new schema for analytics tables)

9.11 Putting It Together

Setting up a new table for a product changelog and inserting audit records:

-- Create schema and table
CREATE SCHEMA audit;

CREATE TABLE audit.product_price_history (
    history_id    INT IDENTITY PRIMARY KEY,
    product_id    INT NOT NULL,
    product_name  VARCHAR(255) NOT NULL,
    old_price     DECIMAL(10, 2) NOT NULL,
    new_price     DECIMAL(10, 2) NOT NULL,
    changed_at    DATETIME2 DEFAULT GETDATE(),
    FOREIGN KEY (product_id) REFERENCES production.products (product_id)
);

-- Record a price change inside a transaction
BEGIN TRANSACTION;

BEGIN TRY
    -- Log the old price before updating
    INSERT INTO audit.product_price_history (product_id, product_name, old_price, new_price)
    SELECT
        product_id,
        product_name,
        list_price,
        list_price * 1.1
    FROM production.products
    WHERE category_id = 6;

    -- Apply the price change
    UPDATE production.products
    SET list_price = list_price * 1.1
    WHERE category_id = 6;

    COMMIT;
    PRINT 'Price update committed successfully.';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Error - transaction rolled back.';
    THROW;
END CATCH;

The transaction ensures both the audit log and the price update either both succeed or both fail. If the UPDATE fails, the INSERT is also rolled back.

Summary

INSERT adds rows one at a time, in bulk, or from a query. UPDATE modifies existing rows and should always include WHERE unless a full-table update is intended. DELETE removes rows; TRUNCATE removes all rows faster with minimal logging. MERGE handles insert, update, and delete against a target in a single statement, the standard pattern for ETL loads. Transactions (BEGIN TRANSACTION, COMMIT, ROLLBACK) group statements so they succeed or fail together. CREATE TABLE defines columns with data types and constraints. Constraints include PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, and DEFAULT. ALTER TABLE modifies an existing table. Schemas organize tables into logical namespaces and simplify permission management.

Exercises

9.1 - Write an INSERT statement that adds a new brand "Specialized" to production.brands. Then write a second INSERT that adds three products for that brand in one statement.

9.2 - Write an UPDATE that increases the list_price by 15% for all Mountain Bikes (category_id = 6) with a model_year of 2018.

9.3 - The following DELETE is missing a WHERE clause. What does it do and how would you fix it to remove only discontinued products (model year before 2016)?

DELETE FROM production.products;

9.4 - Create a production.brands_staging table holding the same columns as production.brands, with brand_id nullable and no IDENTITY. Then write a MERGE statement that synchronizes production.brands from it: update the brand_name when the brand_id matches, insert new brands from staging that do not exist in the main table.

9.5 - Create a table sales.order_notes with columns: note_id (identity primary key), order_id (foreign key to sales.orders), note_text (VARCHAR 500), created_at (DATETIME2 with default GETDATE()), and a CHECK constraint ensuring note_text is not an empty string.

9.6 - Write a transaction that transfers a product from production.stocks at store_id = 1 to store_id = 2 by decrementing quantity in store 1 and incrementing in store 2. Wrap it in TRY/CATCH so either both changes commit or neither does.

9.7 - Think About It: DELETE FROM table and TRUNCATE TABLE table both remove all rows. A developer says they are interchangeable. Identify at least three situations where this claim is wrong and the difference matters.

9.1 INSERT​

Single Row​

Multiple Rows​

INSERT INTO SELECT​

9.2 UPDATE​

UPDATE with JOIN​

9.3 DELETE​

DELETE with JOIN​

TRUNCATE TABLE​

9.4 MERGE​

9.5 Transactions​

9.6 CREATE TABLE​

Creating the BikeStores tables​

9.7 Common Data Types​

9.8 Constraints​

PRIMARY KEY​

FOREIGN KEY​

NOT NULL​

UNIQUE​

CHECK​

DEFAULT​

Constraint Overview​

9.9 ALTER TABLE​

9.10 Schemas​

9.11 Putting It Together​

Summary​

Exercises​