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