Snippets: DML - Data Manipulation Language

Read more about T-SQL Data Manipulation Language

Basic SELECT Pattern

1
2
3
4
5
6
7

SELECT
    t.col1,
    t.col2,
    t.col3
FROM dbo.MyTable AS t
WHERE t.col1 = 'some value'
ORDER BY t.col2;

SELECT with JOIN pattern

1
2
3
4
5
6
7

SELECT
    a.Id,
    a.Name,
    b.Status
FROM dbo.TableA AS a
JOIN dbo.TableB AS b
    ON b.TableAId = a.Id;

SELECT with GROUP BY / HAVING

1
2
3
4
5
6

SELECT
    Category,
    COUNT(*) AS RecordCount
FROM dbo.Items
GROUP BY Category
HAVING COUNT(*) > 1;

INSERT with SELECT

Data comes from another query

1
2
3
4

INSERT INTO dbo.ArchiveTable (Id, [Name])
SELECT Id, [Name]
FROM dbo.SourceTable AS s
WHERE s.IsActive = 0;

INSERT with values

You already know the data

1
2

INSERT INTO dbo.Users ([Name], Email)
VALUES ('Bob', 'bob@example.com');

UPDATE with JOIN pattern

1
2
3
4
5
6
7

UPDATE a
SET a.Status = b.NewStatus
FROM dbo.TableA a
JOIN dbo.TableB b
    ON b.TableAId = a.Id
WHERE b.IsActive = 1
  AND a.Status <> b.NewStatus;

DELETE with JOIN pattern

1
2
3
4
5

DELETE a
FROM dbo.TableA a
JOIN dbo.TableB b
    ON b.TableAId = a.Id
WHERE b.IsExpired = 1;