DML — INSERT, UPDATE, DELETE and OUTPUT

DML (Data Manipulation Language) — INSERT, UPDATE, DELETE — modifies the data in tables. The OUTPUT clause captures what was changed, enabling the calling code to retrieve auto-generated IDs or audit the rows affected. MERGE combines insert and update in one statement for upsert scenarios. Understanding the difference between DELETE (logged row-by-row, honours triggers, can use WHERE) and TRUNCATE (minimal logging, resets identity, cannot use WHERE) guides the right choice for different data removal scenarios.

INSERT, UPDATE, DELETE and OUTPUT #

-- ── INSERT — always specify column list ───────────────────────────────────
INSERT INTO dbo.Posts (AuthorId, Title, Slug, Body, Status)
VALUES (N'user-123', N'Getting Started with .NET', 'getting-started-dotnet',
        N'This post covers...', 'draft');

-- ── INSERT with OUTPUT — capture the generated identity ───────────────────
DECLARE @NewPostId INT;

INSERT INTO dbo.Posts (AuthorId, Title, Slug, Body, Status)
OUTPUT INSERTED.Id INTO @NewPostIdTable(Id)
VALUES (N'user-123', N'My Post', 'my-post', N'Content...', 'draft');

-- ── Bulk INSERT from another query ────────────────────────────────────────
INSERT INTO dbo.PostTags (PostId, TagId)
SELECT p.Id, t.Id
FROM   dbo.Posts p
CROSS JOIN dbo.Tags t
WHERE  p.Id = 42
  AND  t.Slug IN ('dotnet', 'csharp', 'aspnet');

-- ── UPDATE ────────────────────────────────────────────────────────────────
UPDATE dbo.Posts
SET    Title     = N'Updated Title',
       Slug      = 'updated-title',
       UpdatedAt = SYSUTCDATETIME()
WHERE  Id = 42;

-- ── UPDATE with OUTPUT (see both old and new values) ─────────────────────
UPDATE dbo.Posts
SET    IsPublished = 1,
       PublishedAt = SYSUTCDATETIME(),
       UpdatedAt   = SYSUTCDATETIME()
OUTPUT DELETED.IsPublished AS OldIsPublished,
       INSERTED.IsPublished AS NewIsPublished,
       INSERTED.PublishedAt AS PublishedAt
WHERE  Id = 42;

-- ── UPDATE with JOIN (update based on related table) ─────────────────────
UPDATE p
SET    p.ViewCount = p.ViewCount + 1
FROM   dbo.Posts p
JOIN   dbo.PostViewLog v ON v.PostId = p.Id
WHERE  v.ViewedAt >= DATEADD(DAY, -1, GETUTCDATE());

-- ── DELETE ────────────────────────────────────────────────────────────────
DELETE FROM dbo.PostTags WHERE PostId = 42;  -- remove tag associations first
DELETE FROM dbo.Posts   WHERE Id = 42 AND AuthorId = N'user-123';

-- ── TRUNCATE vs DELETE ────────────────────────────────────────────────────
-- DELETE: row-by-row logged, fires triggers, honours foreign keys, can use WHERE
-- TRUNCATE: minimal logging, resets IDENTITY, cannot have active foreign keys, no WHERE
TRUNCATE TABLE dbo.PostViewLog;  -- much faster for clearing entire table

-- ── MERGE (upsert) ────────────────────────────────────────────────────────
MERGE dbo.Tags AS target
USING (VALUES (@TagName, @TagSlug)) AS source (Name, Slug)
ON (target.Slug = source.Slug)
WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name
WHEN NOT MATCHED THEN
    INSERT (Name, Slug) VALUES (source.Name, source.Slug)
OUTPUT $action, INSERTED.Id, INSERTED.Slug;

Common Mistakes #

Mistake 1 — INSERT without column list (breaks when table schema changes) #

❌ Wrong — INSERT INTO Posts VALUES (1, 'Title', ...); fails if a column is added or reordered.

✅ Correct — always specify columns: INSERT INTO Posts (AuthorId, Title, Slug, ...) VALUES (...).

Mistake 2 — UPDATE or DELETE without WHERE (modifies all rows) #

❌ Wrong — UPDATE Posts SET IsPublished = 1; publishes every post in the table.

✅ Correct — verify the WHERE clause with a SELECT first, then apply to UPDATE/DELETE.