INSERT, UPDATE, DELETE in SQL Server: The Mistake That Wipes Your Table

DML (Data Manipulation Language) statements change the data inside tables, not their structure. They’re also where the single most expensive beginner mistake in SQL happens — and where a handful of less-obvious patterns (multi-row inserts, UPDATE with a JOIN, the OUTPUT clause) separate someone who can write basic DML from someone who’s actually fluent.

INSERT: One Row, Many Rows, or From Another Query

-- Single row
INSERT INTO dbo.Employee (first_name, last_name, hire_date, salary)
VALUES ('Priya', 'Sharma', '2024-03-01', 75000.00);

-- Multiple rows in one statement — one round trip instead of three
INSERT INTO dbo.Employee (first_name, last_name, hire_date, salary)
VALUES
    ('Alex', 'Chen', '2024-04-10', 68000.00),
    ('Jordan', 'Lee', '2024-05-02', 71000.00),
    ('Sam', 'Patel', '2024-05-15', 69500.00);

-- INSERT ... SELECT — copy rows from another table/query, no VALUES needed
INSERT INTO dbo.Employee_Archive (first_name, last_name, hire_date, salary)
SELECT first_name, last_name, hire_date, salary
FROM dbo.Employee
WHERE hire_date < '2020-01-01';

INSERT ... SELECT is one of the most-used patterns in real T-SQL — archiving old rows, seeding a new table from an existing one, or building the exact kind of synthetic test data later chapters use for performance work.

UPDATE and DELETE: The Basics

UPDATE dbo.Employee
SET salary = salary * 1.05
WHERE last_name = 'Sharma';

DELETE FROM dbo.Employee
WHERE employee_id = 2;

TRUNCATE TABLE dbo.Employee; -- wipes ALL rows instantly, resets IDENTITY

UPDATE Driven by Another Table

A pattern beginners often don't discover for a while: updating one table based on values in another, using a JOIN directly inside the UPDATE:

UPDATE e
SET e.salary = e.salary * 1.10
FROM dbo.Employee e
JOIN dbo.Department d ON d.department_id = e.department_id
WHERE d.name = 'Engineering';

This gives every Engineering employee a 10% raise in one statement, without needing to first SELECT the matching IDs into a list. You'll get comfortable with the JOIN syntax itself in Chapter 5 — file this pattern away for later.

DELETE vs TRUNCATE: Not Interchangeable

Rule of thumb: reach for TRUNCATE only when you genuinely mean "empty this entire table and I don't care about triggers or per-row logging" — typically scratch/staging tables. For anything with a WHERE clause, or that has triggers or dependent foreign keys, DELETE is the only option anyway.

The Mistake That Costs Careers

Without a WHERE clause, UPDATE and DELETE apply to every row in the table. Always verify your WHERE clause as a SELECT first:

-- Step 1: verify what you're about to change
SELECT * FROM dbo.Employee WHERE last_name = 'Sharma';

-- Step 2: only then run the UPDATE with the identical WHERE clause
UPDATE dbo.Employee SET salary = salary * 1.05 WHERE last_name = 'Sharma';

A Safer Habit: OUTPUT and Transactions

Two techniques that make destructive DML meaningfully safer in practice. First, OUTPUT shows you exactly what changed, immediately:

UPDATE dbo.Employee
SET salary = salary * 1.05
OUTPUT deleted.employee_id, deleted.salary AS old_salary, inserted.salary AS new_salary
WHERE last_name = 'Sharma';

Second, wrap risky DML in an explicit transaction so a mistake is one ROLLBACK away instead of permanent:

BEGIN TRAN;
UPDATE dbo.Employee SET salary = 0; -- oops, forgot the WHERE clause
SELECT * FROM dbo.Employee; -- immediately obvious something's wrong
ROLLBACK; -- undone, no harm done

CREATE, ALTER, DROP: SQL Server DDL Explained With Examples

DDL (Data Definition Language) statements define the structure of your database — tables, columns, and their types. Every DDL statement in SQL Server takes effect immediately and, unusually among major databases, is fully transactional — you can wrap DDL in BEGIN TRAN/ROLLBACK and undo it, which most other RDBMSes don’t allow.

CREATE: Build a New Table

CREATE TABLE dbo.Employee (
    employee_id   INT IDENTITY(1,1) PRIMARY KEY,
    first_name    NVARCHAR(50)  NOT NULL,
    last_name     NVARCHAR(50)  NOT NULL,
    hire_date     DATE          NOT NULL DEFAULT GETDATE(),
    salary        DECIMAL(10,2) NOT NULL
);

Notice the dbo. prefix — that’s the schema name. dbo (database owner) is the default schema every database starts with; schemas are namespaces that let you group related tables (e.g. sales.Order vs hr.Employee) and manage permissions per group. Always schema-qualify your table names in real code — unqualified names resolve against whatever the current user’s default schema happens to be, which is a subtle source of “works on my machine” bugs.

Guarding CREATE Against Re-Running a Script

CREATE TABLE IF NOT EXISTS dbo.Scratch_Test (id INT); -- NOT valid T-SQL!

-- The actual SQL Server idiom:
IF OBJECT_ID('dbo.Scratch_Test', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.Scratch_Test (id INT);
END

ALTER: Change an Existing Table

ALTER TABLE dbo.Employee ADD email NVARCHAR(100) NULL;
ALTER TABLE dbo.Employee ALTER COLUMN salary DECIMAL(12,2) NOT NULL;
ALTER TABLE dbo.Employee DROP COLUMN email;

Two things about ALTER COLUMN that surprise beginners: first, widening a type (DECIMAL(10,2) → DECIMAL(12,2), or VARCHAR(50) → VARCHAR(100)) is safe and fast. Narrowing one — or changing NULL to NOT NULL on a column that already has NULL values — fails outright if existing data can’t fit the new definition. SQL Server checks every existing row before allowing the change.

-- This fails if any existing row has a NULL email:
ALTER TABLE dbo.Employee ALTER COLUMN email NVARCHAR(100) NOT NULL;
-- Msg 515: Cannot insert the value NULL into column 'email' ...

-- The real-world fix: clean the data first, then tighten the constraint
UPDATE dbo.Employee SET email = 'unknown@example.com' WHERE email IS NULL;
ALTER TABLE dbo.Employee ALTER COLUMN email NVARCHAR(100) NOT NULL;

Renaming Things (It’s Not ALTER)

Unlike some databases, T-SQL doesn’t rename objects through ALTER — it uses a dedicated system procedure:

EXEC sp_rename 'dbo.Employee.email', 'contact_email', 'COLUMN';
EXEC sp_rename 'dbo.Employee', 'Staff';

DROP: Permanently Remove an Object

DROP TABLE IF EXISTS dbo.Scratch_Test;

Unlike CREATE TABLE IF NOT EXISTS, DROP TABLE IF EXISTS genuinely is valid modern T-SQL (SQL Server 2016+) — the asymmetry is just a quirk of which syntax Microsoft added and when.

The Danger Zone, Visualized

DROP TABLE deletes the table and every row in it, permanently, with no confirmation prompt. Always double-check you’re connected to the right database (SELECT DB_NAME();) before running DROP anywhere near a real environment.

DDL Is Transactional — Use It

Because DDL participates in transactions in SQL Server, you can test a risky structural change safely:

BEGIN TRAN;

ALTER TABLE dbo.Employee DROP COLUMN salary;
SELECT * FROM dbo.Employee; -- confirm it looks right

ROLLBACK; -- changed your mind — salary column is back, nothing happened
-- or: COMMIT; -- to make it permanent