INSERT, UPDATE, DELETE & the OUTPUT Clause

T-SQL's DML goes beyond ANSI standard with two big additions: the UPDATE ... FROM / DELETE ... FROM extensions for set-based updates from joins, and the OUTPUT clause for capturing affected rows.

INSERT

-- Single row, all columns in declared order
INSERT INTO departments VALUES (270, 'Payroll', 200, 1700);

-- Single row, named columns (preferred — survives schema changes)
INSERT INTO departments (department_id, department_name, location_id)
VALUES (280, 'Procurement', 1700);

-- Multiple rows in one statement
INSERT INTO departments (department_id, department_name, location_id) VALUES
    (290, 'Risk',       1700),
    (300, 'Compliance', 1700),
    (310, 'Audit',      1700);

-- INSERT ... SELECT — copy from another query
INSERT INTO employees_archive (employee_id, first_name, last_name, salary, archived_at)
SELECT  employee_id, first_name, last_name, salary, GETDATE()
FROM    employees
WHERE   hire_date < '2010-01-01';

-- INSERT ... EXEC — capture stored proc output as rows
INSERT INTO #plan_log (plan_handle, sql_text)
EXEC sp_get_query_stats @database_id = 7;

UPDATE

-- Standard ANSI update
UPDATE employees
SET    salary = salary * 1.05
WHERE  department_id = 50;

-- T-SQL UPDATE ... FROM — update from a join
UPDATE  e
SET     e.salary = e.salary * b.bonus_pct
FROM    employees e
JOIN    department_bonus b ON b.department_id = e.department_id
WHERE   b.year = 2024;

-- Multiple columns
UPDATE employees
SET    salary = 9000,
       commission_pct = 0.10,
       updated_at = SYSDATETIME()
WHERE  employee_id = 100;

-- Conditional update with CASE
UPDATE employees
SET salary =
    CASE
        WHEN years_of_service >= 10 THEN salary * 1.10
        WHEN years_of_service >=  5 THEN salary * 1.05
        ELSE salary * 1.02
    END
WHERE department_id = 80;

DELETE

-- Simple delete
DELETE FROM orders WHERE order_date < '2010-01-01';

-- T-SQL DELETE ... FROM — delete based on a join
DELETE  o
FROM    orders        o
JOIN    customers     c ON c.customer_id = o.customer_id
WHERE   c.region = 'Inactive';

-- Delete all rows (logged), or use TRUNCATE for minimally-logged reset
DELETE FROM #scratch;
TRUNCATE TABLE #scratch;        -- faster, resets identity, requires no FK references

The OUTPUT Clause

OUTPUT returns rows affected by the DML and references two virtual tables:

OUTPUT to the Caller

-- Capture inserted rows back to the caller (acts like a SELECT)
INSERT INTO departments (department_id, department_name, location_id)
OUTPUT inserted.department_id, inserted.department_name
VALUES (320, 'Innovation', 1700);

-- Update with before/after view
UPDATE employees
SET    salary = salary * 1.10
OUTPUT  deleted.employee_id,
        deleted.salary  AS old_salary,
        inserted.salary AS new_salary
WHERE  department_id = 60;

-- DELETE returning what was removed
DELETE FROM orders
OUTPUT deleted.*
WHERE  order_date < '2015-01-01';

OUTPUT INTO a Table or Variable

Use OUTPUT ... INTO to persist rows for downstream processing — typically an audit table:

DECLARE @audit TABLE (
    employee_id INT,
    old_salary  DECIMAL(10,2),
    new_salary  DECIMAL(10,2),
    changed_at  DATETIME2
);

UPDATE  employees
SET     salary = salary * 1.10
OUTPUT  deleted.employee_id,
        deleted.salary,
        inserted.salary,
        SYSDATETIME()
INTO    @audit
WHERE   department_id = 60;

SELECT * FROM @audit;

Audit Pattern

CREATE TABLE dbo.salary_audit (
    audit_id     BIGINT IDENTITY(1,1) PRIMARY KEY,
    employee_id  INT,
    old_salary   DECIMAL(10,2),
    new_salary   DECIMAL(10,2),
    changed_by   SYSNAME      DEFAULT SUSER_SNAME(),
    changed_at   DATETIME2    DEFAULT SYSDATETIME()
);

UPDATE  employees
SET     salary = salary + 500
OUTPUT  deleted.employee_id, deleted.salary, inserted.salary
INTO    dbo.salary_audit (employee_id, old_salary, new_salary)
WHERE   employee_id = 100;

Returning the Identity Just Inserted

Combine OUTPUT with INSERT to get the new identity value reliably (no @@IDENTITY race conditions):

DECLARE @new_ids TABLE (id INT);

INSERT INTO products (sku, name, list_price)
OUTPUT inserted.product_id INTO @new_ids
VALUES ('ABC-100', 'Widget', 9.99),
       ('ABC-101', 'Gadget', 14.99);

SELECT id FROM @new_ids;

Best Practices

• Always list target columns in INSERT.
• Watch out for non-deterministic UPDATE ... FROM when the join is many-to-one in the wrong direction.
• Prefer OUTPUT INTO audit tables over triggers when the audit lives next to the originating statement — it's faster and easier to reason about.
• Use TRUNCATE TABLE for full-reset performance, but remember it cannot fire DML triggers and requires no incoming FK references.

Summary

• INSERT ... VALUES, INSERT ... SELECT, and INSERT ... EXEC all populate tables.
• T-SQL's UPDATE ... FROM and DELETE ... FROM enable join-based set updates.
• OUTPUT exposes inserted and deleted virtual tables for the affected rows.
• OUTPUT ... INTO is the canonical way to capture identity values and build audit pipelines without triggers.