Skip to content

DML — Data Manipulation Language

1. INSERT

-- Basic
INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...);

-- Example
INSERT INTO payments (customerNumber, checkNumber, paymentDate, amount)
VALUES (103, 'HQ336336', '2021-10-19', 6066.78);

INSERT SELECT

INSERT INTO #belgianCustomers (customerName, city)
SELECT customerName, city
FROM   customers
WHERE  country = 'Belgium';

2. UPDATE

UPDATE customers
SET    creditLimit = 10000
WHERE  customerNumber = 103;

UPDATE with JOIN

UPDATE e
SET    e.salary = e.salary * 1.1
FROM   employee e
JOIN   department d ON d.id = e.dep_id
WHERE  d.dep_name = 'Sales';

3. DELETE

-- A specific row
DELETE FROM customers WHERE customerNumber = 103;

-- All rows
DELETE FROM customers;

Danger

DELETE FROM table without a WHERE removes all rows.

4. T-SQL Functions

Function Description
CONCAT('hello', 'tokyo') Concatenate strings
SUBSTR('12345', 2, 3) → '234' Get part of a string
COALESCE(NULL, NULL, 'a', 'b') → 'a' First non-NULL value
GETDATE() Current date and time
COUNT(*) Number of records
SUM() Sum
AVG() Average

5. Useful Operators

Operator Description
= < > <> >= <= Comparison
LIKE '%hello%' % = 0+ characters, _ = 1 character
IS NULL Never use = NULL
AND / OR / NOT Logical operators
IN (a, b, c) Is one of these values

6. Session Variables

DECLARE @variable datatype;
SET     @variable = value;

-- or via SELECT
SELECT  @variable = column FROM table;
SELECT  @variable;

USE ClassicModels;
GO

DECLARE @numberCustomers INT;
SELECT  @numberCustomers = COUNT(customerName) FROM Customers;
SELECT  @numberCustomers;

DECLARE @numberEmployees INT = 0;
SET     @numberEmployees = (SELECT COUNT(*) FROM Employees);
SELECT  @numberEmployees;

7. IF – ELSE

IF (@num1 > @num2)
    PRINT 'The 1st number is bigger.'
ELSE IF (@num2 > @num1)
    PRINT 'The 2nd number is bigger.'
ELSE
    PRINT 'The numbers are equal.';

Nested IF – ELSE

IF (@num1 > @num2)
BEGIN
    PRINT 'The 1st number is bigger.';
    IF (@num1 > 75)
        PRINT 'The 1st number is bigger than 75.';
    ELSE IF (@num1 > 50)
        PRINT 'The 1st number is bigger than 50.';
    ELSE
        PRINT 'The 1st number is less than or equal to 50.';
END
ELSE IF (@num2 > @num1)
    PRINT 'The 2nd number is bigger.'
ELSE
    PRINT 'The numbers are equal.';

8. CASE Statement

Returns a value based on a condition. Usable in SELECT, WHERE, ORDER BY, GROUP BY, UPDATE and INSERT.

SELECT EmployeeName,
       CASE
           WHEN Salary >= 80000 AND Salary <= 100000 THEN 'Director'
           WHEN Salary >= 50000 AND Salary <  80000  THEN 'Senior Consultant'
           ELSE 'Employee'
       END AS Position
FROM Employee;

SELECT EmployeeName, Gender, Salary
FROM   Employee
ORDER BY
    CASE Gender WHEN 'F' THEN Salary END DESC,
    CASE WHEN Gender = 'M' THEN Salary END;

SELECT CASE
           WHEN Salary >= 80000 AND Salary <= 100000 THEN 'Director'
           WHEN Salary >= 50000 AND Salary <  80000  THEN 'Senior Consultant'
           ELSE 'Employee'
       END AS Position,
       MIN(Salary) AS 'Minimum Salary',
       MAX(Salary) AS 'Maximum Salary'
FROM   Employee
GROUP BY
    CASE
        WHEN Salary >= 80000 AND Salary <= 100000 THEN 'Director'
        WHEN Salary >= 50000 AND Salary <  80000  THEN 'Senior Consultant'
        ELSE 'Employee'
    END;

UPDATE employee
SET StateCode = CASE StateCode
    WHEN 'Ar' THEN 'FL'
    WHEN 'GE' THEN 'AL'
    ELSE 'IN'
END;

INSERT INTO employee
VALUES (
    @EmployeeName,
    CASE @Gender WHEN 0 THEN 'M' WHEN 1 THEN 'F' END,
    @Statecode,
    @salary
);

9. WHILE Loop

SQL Server has one loop construct: the WHILE loop.

WHILE {condition}
BEGIN
    -- statements
END;

DECLARE @i INT;
SET @i = 1;

WHILE @i <= 10
BEGIN
    PRINT CONCAT('Pass...', @i);
    SET @i = @i + 1;
END;

BREAK and CONTINUE

Keyword Behavior
BREAK Exits the loop immediately
CONTINUE Skips the remaining statements and moves to the next iteration

Warning

CONTINUE can cause an infinite loop if the counter update comes after it.

10. Transactions & Rollbacks

What Is a Transaction?

A sequence of SQL statements executed as one unit — either fully successful or fully rolled back.

Types of Transactions

Type Description
Autocommit Default — every statement is its own transaction
Implicit SQL Server automatically starts a transaction on every DML — closed with COMMIT or ROLLBACK
Explicit You determine the start and end

Implicit Transaction

SET IMPLICIT_TRANSACTIONS ON;
-- ... statements ...
COMMIT TRANSACTION;   -- or ROLLBACK TRANSACTION

SELECT @@TRANCOUNT AS 'open transaction(s)';

Explicit Transaction

BEGIN TRANSACTION;           -- or: BEGIN TRANSACTION updatePerson;
-- ... statements ...
COMMIT TRANSACTION;          -- or: ROLLBACK TRANSACTION;

Save Points

Rolling back part of a transaction:

BEGIN TRANSACTION;

INSERT INTO [Person] VALUES ('Mouse', 'Micky', '500 South Buena Vista Street', 'California', 43);
SAVE TRANSACTION InsertStatement;

DELETE Person WHERE PersonId = 3;

ROLLBACK TRANSACTION InsertStatement;  -- only undoes the DELETE
COMMIT;

Auto Rollback

If one statement in a transaction produces an error → all changes in that transaction are rolled back.

Marked Transactions

BEGIN TRAN DeletePerson WITH MARK 'MarkedTransactionDescription';
DELETE Person WHERE PersonID BETWEEN 3 AND 4;
COMMIT TRAN DeletePerson;

-- Querying
USE [msdb];
SELECT * FROM msdb.dbo.logmarkhistory;

11. Cursors

A cursor processes query results row by row instead of as a set.

Cursors are slow

As long as a cursor is open, the table is locked for other users. Replace cursors with JOINs where possible.

5 Steps

-- 1. Declare
DECLARE EmpCursor CURSOR FOR
    SELECT id, name FROM employee;

-- 2. Open
OPEN EmpCursor;

-- 3. Fetch the first row
FETCH NEXT FROM EmpCursor INTO @EmpId, @EmpName;

-- 4. Process
WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT 'Employee Id = ' + CAST(@EmpId AS NVARCHAR(10)) + ' | Name = ' + @EmpName;
    FETCH NEXT FROM EmpCursor INTO @EmpId, @EmpName;
END;

-- 5. Close and release
CLOSE      EmpCursor;
DEALLOCATE EmpCursor;

@@FETCH_STATUS

Value Meaning
0 FETCH succeeded
-1 FETCH failed or the row is outside the result set
-2 The fetched row is missing
-9 The cursor performs no FETCH

Replacing a Cursor with a JOIN

UPDATE employee
SET salary = CASE
    WHEN dep_name = 'Sales' THEN (salary * 1.1)
    WHEN dep_name = 'HR'    THEN (salary * 1.2)
    WHEN dep_name = 'IT'    THEN (salary * 1.5)
END
FROM   employee
INNER JOIN department ON department.id = employee.dep_id
WHERE  dep_name IN ('Sales', 'HR', 'IT');